Find the year in which total customers (sum) was higher

Question

I’m using sqlite, and I have a table like:

year | clients    
---- | -------                                                            
2000 | 1                                                                              
2000 | 2  
1999 | 3                                                                      
1999 | 5  
2000 | 4                                                                      
1999 | 6

This is just one example. I want to find the year when the total of customers was higher.

The best I could do was to separate for each year, add up the clients, and present each year with the respective sum. But I don’t know how to present only the year it was greatest.

I want to present the year and total customers. That is to say:

1999 | 14                                                                     

I used:

select year, sum(clients) from table group by year;

I’ve tried too

select year, max(sum(clients)) from table group by year;

Answer 1

The first select is correct according to the result example you put in the question.

If the table name is really table (by the way, the name is terrible) you should escape the name using backticks because this is a reserved word. That is, it is necessary to use `table` instead of table.

If you need to present only the first result, use limit 1 at the end of query. In this case, it is a good idea to force ordering by the column in which the client sum is made.

select year, sum(clients) as soma 
from `table`
group by year 
order by soma desc
limit 1

See working on Sqlfiddle.