Ash Ash - 22 days ago 8
SQL Question

How to order by the sum of multiple columns

I have a query that calculates the total sales by month,

the basics of the select clause looks like this

SELECT
MID.menu_item_id,
ISNULL(SUM(ISNULL(CASE WHEN CSD.tendered_date_time BETWEEN
CONVERT(datetime,'2016-07-01 06:00:00:000') AND CONVERT(datetime,'2016-08-01
05:59:59:999')THEN CID.consumed_quantity END,0)),0)
AS [JUL],


This cycles through various month. I would like to sum the total across all of these months and then order by this sum in a descending manner - so we have a list of menu_item_ids ordered by their total sales.

Any ideas how to do this gents?

Answer Source

You would add another column:

SELECT MID.menu_item_id,
       SUM(CID.consumed_quantity) as total
       SUM(CASE WHEN CSD.tendered_date_time BETWEEN 
CONVERT(datetime,'2016-07-01 06:00:00.000') AND CONVERT(datetime, '2016-08-01 
05:59:59.999') THEN CID.consumed_quantity ELSE 0 END) AS [JUL],
       . . .
. . .
ORDER BY SUM(CID.consumed_quantity) DESC;