CODEWITHSUNDEEP
sqlsql-serversql-order-by
Asher
Asher

Reputation: 367

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.

Upvotes: 0

Views: 621

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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;

Upvotes: 4

Related Questions