MariaDB : Sorting with SUM, Group, DESC does not work as I intended. (exercising with Headfirst SQL)

Question

I have a list of products which is sold and i am trying to make SUM list sorted by seller and amount of price but it does not work as written in the book.

i am reading HeadFirst SQL(which is about MySQL) and what i am trying isn't working as statements written in book. I am practicing with MariaDB but MariaDB and MySQL is almost similar so i think matter doesn't comes frime that difference and i cannot figure out why this happen.

This is information about COLUMNS


MariaDB [headfirstdrinks]> DESC movie_table;
+-----------+--------------+------+-----+----------+----------------+
| Field     | Type         | Null | Key | Default  | Extra          |
+-----------+--------------+------+-----+----------+----------------+
| movie_id  | int(11)      | NO   | PRI | NULL     | auto_increment |
| title     | varchar(50)  | NO   |     | NULL     |                |
| rating    | varchar(2)   | NO   |     | NULL     |                |
| category  | varchar(15)  | NO   |     | misc     |                |
| purchased | date         | NO   |     | NULL     |                |
| seller    | varchar(15)  | NO   |     | In Stock |                |
| price     | decimal(5,2) | NO   |     | 0.00     |                |
+-----------+--------------+------+-----+----------+----------------+

And This is Table

MariaDB [headfirstdrinks]> SELECT title, seller, price FROM movie_table;
+------------------------------+----------+-------+
| title                        | seller   | price |
+------------------------------+----------+-------+
| Monsters, inc.               | Jessie   |  2.00 |
| The Godfather                | Jessie   | 15.00 |
| Gone with the Wind           | Jessie   | 11.00 |
| American Pie                 | Jessie   |  8.00 |
| Nightmare on Eim Street      | Jessie   |  5.00 |
| Casablanca                   | Nicole   | 11.00 |
| Big Adventure                | Nicole   |  2.00 |
| Greg:The Untold Story        | Nicole   |  8.00 |
| Mad Clowns                   | In Stock | 11.00 |
| Paraskavendekariaphobia      | Brown    |  5.00 |
| Rat named Darcy,A            | Brown    |  2.00 |
| End of the Line              | Brown    |  8.00 |
| Shark Bait                   | Brown    |  2.00 |
| Angry Pirate                 | Brown    | 15.00 |
| Potentially Habitable Planet | Brown    |  5.00 |
+------------------------------+----------+-------+

SELECT seller, SUM(price) FROM movie_table ORDER BY SUM(price) DESC, seller;
+--------+------------+
| seller | SUM(price) |
+--------+------------+
| Jessie |     110.00 |
+--------+------------+
\\There's no Nicole and Brown (i don't know why,
\\but seems like this is caused because SUM is a function ),
\\so i tried "GROUP BY"

SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price DESC;
+----------+------------+
| seller   | SUM(price) |
+----------+------------+
| In Stock |      11.00 |
| Nicole   |      21.00 |
| Brown    |      37.00 |
| Jessie   |      41.00 |
+----------+------------+
\\this is ascending even though i put DESC so i put ASC

SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price ASC, seller;
+----------+------------+
| seller   | SUM(price) |
+----------+------------+
| Jessie   |      41.00 |
| Brown    |      37.00 |
| In Stock |      11.00 |
| Nicole   |      21.00 |
+----------+------------+
\\and this is even not Ascending , as i see 'In Stock' upper than 'Nicole'

which thing did I do wrong???

Answer 1

you missed group by

SELECT seller, SUM(price) 
FROM movie_table 
GROUP BY seller
ORDER BY SUM(price) DESC, seller;

and without group by using a mysql version previous then 5.7 i returned jusy one rows with casual value for seller ( for others version by default raise an error)

for others queries (wrong order by) you should use the proper column name ( sum(price) ) or an alias for the column name

SELECT seller, SUM(price) my_col
FROM movie_table 
GROUP BY seller ORDER BY my_col ASC, seller;

or

SELECT seller, SUM(price) my_col
FROM movie_table 
GROUP BY seller ORDER BY SUM(price ASC, seller;

Answer 2

This query:

SELECT seller, SUM(price) FROM movie_table ORDER BY SUM(price) DESC, seller;

is logically wrong, because you don't specify a column on which you could group to get the sum, so you get the whole table's sum with a name picked by MySQL.
In these queries:

SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price DESC;
SELECT seller, SUM(price) FROM movie_table GROUP BY seller ORDER BY price ASC, seller;

you do a grouping but you order by a non aggregated column.
You can alias the sums like these and then order by them:

SELECT seller, SUM(price) as price FROM movie_table GROUP BY seller ORDER BY price DESC;
SELECT seller, SUM(price) as price FROM movie_table GROUP BY seller ORDER BY price ASC, seller;

Answer 3

You may want to use alias:

SELECT seller, SUM(price) AS price
FROM movie_table 
GROUP BY seller 
ORDER BY price ASC, seller;