SQL ORDER BY - Why is it not working here?

Question

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.

@jarlh - the joy of MySQLs "do anything, no matter how sane, just to be able to return a result set" philosophy, as opposed to most other database systems "that's clearly not what you wanted to do, have an error" — Damien_The_Unbeliever, Aug 25 '17 at 09:27
@jarlh I tried `order by 'total quant'` but it did not work. — CN1002, Aug 25 '17 at 09:32

score 8 · Accepted Answer · answered Aug 25 '17 at 09:26

8

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;

answered Aug 25 '17 at 09:26

Gordon Linoff

1,242,037
58
646
786

I got it, But why does it not recognize `order by 'total quant '`? Why does it fail in escaping? – CN1002 Aug 25 '17 at 09:35
Thanks for explaining but may you shade light on `arbitrary value on each group`. Do you mean the variable `quantity` is given assigned some values that are not in the resulset? – CN1002 Aug 25 '17 at 09:41
1

@Giovanrich You should use backticks to escape column names and aliases. Single quotes create literal strings. See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Barmar Aug 25 '17 at 09:48
@Giovanrich There's no `quantity` in the resultset, that column is just in the table rows. For each group, it selects `quantity` from an arbitrary row and orders by that. – Barmar Aug 25 '17 at 09:49

score 3 · Answer 2 · answered Aug 25 '17 at 09:25

3

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;

answered Aug 25 '17 at 09:25

Matt

14,906
27
99
149

score 1 · Answer 3 · edited Aug 25 '17 at 13:25

1

You should use:

order by sum(quantity)

edited Aug 25 '17 at 13:25

cs95

379,657
97
704
746

answered Aug 25 '17 at 10:05

Jason

21
6

SQL ORDER BY - Why is it not working here?

3 Answers3

Linked