CODEWITHSUNDEEP
mysqlsql
RAVI507
RAVI507

Reputation: 129

Query to retrieve the top 2 columns which has the sum greater than the rest

I have one table which contains 5 rows a,b,c,d,e and i need to select the top 2 rows who's sum is greatest.

a b c d e 
1 3 4 5 6
8 6 7 8 9
5 4 9 0 1

so i need to get like 

c  e
20 16

so i tried like 

select top 2* sum(a),sum(b),sum(c),sum(d),sum(e) from tablename;

Upvotes: 2

Views: 45

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Consider restructuring your data as follows:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,x CHAR(1) NOT NULL
,n INT NOT NULL
);

INSERT INTO my_table (x,n) VALUES
('a',1), 
('a',8), 
('a',5), 
('b',3), 
('b',6), 
('b',4), 
('c',4), 
('c',7), 
('c',9), 
('d',5), 
('d',8), 
('d',0), 
('e',6), 
('e',9), 
('e',1);

SELECT * FROM my_table;
+----+---+---+
| id | x | n |
+----+---+---+
|  1 | a | 1 |
|  2 | a | 8 |
|  3 | a | 5 |
|  4 | b | 3 |
|  5 | b | 6 |
|  6 | b | 4 |
|  7 | c | 4 |
|  8 | c | 7 |
|  9 | c | 9 |
| 10 | d | 5 |
| 11 | d | 8 |
| 12 | d | 0 |
| 13 | e | 6 |
| 14 | e | 9 |
| 15 | e | 1 |
+----+---+---+

Then the problem becomes trivial...

SELECT x
     , SUM(n) total_n 
  FROM my_table 
 GROUP 
    BY x 
 ORDER 
    BY total_n DESC 
 LIMIT 2;
+---+---------+
| x | total_n |
+---+---------+
| c |      20 |
| e |      16 |
+---+---------+

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

You can use union to retrieve the columns with the 2 highest sums

select colName, colSum from (
    select 'a' colName, sum(a) colSum from mytable
    union all select 'b' colName, sum(b) colSum from mytable
    union all select 'c' colName, sum(c) colSum from mytable
    union all select 'd' colName, sum(d) colSum from mytable
    union all select 'e' colName, sum(e) colSum from mytable
) t1 order by colSum desc limit 2

However, this will return the values as two rows instead of one as in your example

colName, colSum
c, 20
e, 16

Upvotes: 1

Related Questions