Deven Deven - 3 years ago 184
SQL Question

How to group by sum without using sum function in select?

There's a customer table having id, firstname, country and invoice total( for each customer). I need to write a query displaying list of countries (in descending order) according to revenue generated by each country.
 expected result
I'm trying the following the following query:

Select country, sum(invoicetotal) from customer group by country order by 2 desc

result of above query

The output is according to the expected result but with an additional column of sum(invoicetotal). I cannot figure a way to order the countries according to the sum of their imvoicetotal without using sum() function in select.

Answer Source

You should be able to do:

select country
from t
group by country
order by sum(revenue) desc;

I think all databases support the use of aggregation functions in the order by even if the value is not in the select.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download