how to sort the results of select sum group by

searain

$sql="select sum(product.quantity) from sales group by product_code";

How to sort the results to list by sum of product.quantity from highest to lowest?

Is there a sql command to do that? Or if there is no sql command to do the sort order display, I will have to use the array sort functions to achieve the same results?

Thanks!

Shrike

Something like...

$sql="SELECT SUM(product.quantity) AS prodsum
FROM sales
GROUP BY product_code
ORDER BY prodsum DESC";

searain

I tried, it gave me error said no coloum named prodsum found. It seems that in the group by I cannot use the order by to it.

Shrike

Sorry I didn't really pay much attention to the detail of the SQL. If you are calculating the sum of a column then surely there will only be one piece of data, and so why would you need to order it?

As to the error, it looks as if the order by clause must use an actual column name.

searain

I am calculating the sum of product_quantity and group by product code, so it will be an array of data.

product_code quantity
A 3
A 2
B 1
B 4

The query results are

A 5
B 5

searain

OK it is solved.

$sql="select sum(product.quantity) as prod_quantity from sales group by product_code order by prod_quantity"; is not working

But,

$sql="select sum(product.quantity) as prod_quantity from sales group by product_code order by sum(product.quantity)"; is working.

I thought it was the same, but it is not. Anyone knows why? Thanks!