CODEWITHSUNDEEP
mysqlsqlsql-order-by
CN1002
CN1002

Reputation: 1115

SQL ORDER BY - Why is it not working here?

This is the table I have:

+---------------------+--------+----------+
| date                | sku    | quantity |
+---------------------+--------+----------+
| 2017-08-23 14:58:00 | 123333 |        2 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123337 |        2 |
| 2017-08-23 14:58:00 | 123335 |        1 |
| 2017-08-23 14:58:00 | 123331 |        1 |
| 2017-08-23 14:58:00 | 123332 |        2 |
| 2017-08-23 14:58:00 | 123333 |        1 |
| 2017-08-23 14:58:00 | 123334 |        1 |
| 2017-08-23 14:58:00 | 123334 |        3 |
+---------------------+--------+----------+

I would like to perform an select statement as follows:

select sku,sum(quantity) as 'total quant' from transactions
where DATE(date) between '2017-07-23' and '2017-09-23'
group by sku
order by quantity desc;

But I am getting this:

+--------+-------------+
| sku    | total quant |
+--------+-------------+
| 123332 | 2           |
| 123337 | 2           |
| 123333 | 5           |
| 123334 | 4           |
| 123335 | 1           |
| 123331 | 1           |
+--------+-------------+

Which is NOT in the order as I am expecting. Why is order by not working here?

SIDE NOTE My quantitycolumn is of data type int and I have tried casting as suggested in this post but to no avail.

Upvotes: 0

Views: 8005

Answers (3)

Jason
Jason

Reputation: 21

You should use:

order by sum(quantity)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You are ordering by quantity. Because it is not aggregated, you are getting an arbitrary value from each group. You need to order by the total. One method is:

order by sum(quantity) desc

However, I would recommend assigning a reasonable alias (one that doesn't need to be escaped) and using that:

select sku,sum(quantity) as total_quantity
from transactions
where DATE(date) between '2017-07-23' and '2017-09-23'
group by sku
order by total_quantity desc;

Upvotes: 8

Matt
Matt

Reputation: 15061

You need to order it by the SUM(quantity).

SELECT sku, SUM(quantity) AS 'total quant' 
FROM transactions
WHERE DATE(date) BETWEEN '2017-07-23' AND '2017-09-23'
GROUP BY sku
ORDER BY SUM(quantity) DESC;

Upvotes: 3

Related Questions