CODEWITHSUNDEEP
mysql
user2149346
user2149346

Reputation: 295

mysql order by SUM(column) group'd by another column

i have the following database table: 

create table table1 (
col1 VARCHAR(5) NOT NULL,
col2 VARCHAR(5) NOT NULL,
col3 TINYINT NOT NULL,
col4 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

INSERT INTO table1 VALUES
('b','c',1,NULL),
('b','d',2,NULL),
('a','e',1,NULL),
('a','f',3,NULL); 

mysql> select * from table1;
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| b    | c    |    1 |    1 |
| b    | d    |    2 |    2 |
| a    | e    |    1 |    3 |
| a    | f    |    3 |    4 |
+------+------+------+------+
4 rows in set (0.00 sec)

i want to create a query that orders rows by SUM(col3) GROUP'd on col1. i then need to ORDER col3 DESC.

So final table will look like:
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| a    | f    |    3 |    4 |
| a    | e    |    1 |    3 |
| b    | d    |    2 |    2 |
| b    | c    |    1 |    1 |
+------+------+------+------+

i can get the SUM(col1):
mysql> select sum(col3) from table1 group by col1;
+-----------+
| sum(col3) |
+-----------+
|         4 |
|         3 |
+-----------+

but not sure how to proceed. Any help appreciated.

Upvotes: 1

Views: 63

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

One option would be to join to a subquery which finds the sums:

SELECT t1.*
FROM table1 t1
INNER JOIN
(
    SELECT col1, SUM(col3) AS col3_sum
    FROM table1
    GROUP BY col1
) t2
    ON t1.col1 = t2.col1
ORDER BY
    t2.col3_sum DESC,
    t1.col1,
    t1.col3 DESC;

If you are using MySQL 8+ or later, then we can try using SUM as an analytic function in the ORDER BY clause:

SELECT *
FROM table1
ORDER BY
    SUM(col3) OVER (PARTITION BY col1) DESC,
    col1,
    col3 DESC;

enter image description here

Demo

Upvotes: 2

Chris Lear
Chris Lear

Reputation: 6742

Here's what I came up with:

select table1.* 
from table1
join (
    select col1,sum(col3) as col5
    from table1
    group by col1
     ) t2
on table1.col1=t2.col1
order by col5 desc, col3 desc;

But I think @tim-beigeleisen got there first :)

Upvotes: 0

Related Questions