CODEWITHSUNDEEP
mysqlsqlsql-order-by
MarcoPolo
MarcoPolo

Reputation: 83

Finding sum of top two columns and how much points they have?

Trying to find top 2 column with sum function.

Columns can be either 0 or 1 integer value.

Table can be seen as:

ID  C_ONE C_TWO C_THREE
1     1     1     0
2     0     1     1
3     1     1     0
4     0     0     0
5     1     1     0

C_TWO column has 4 points and C_ONE column has 3 points which are the top 2 columns and their values respectively.

I have stucked with this codes.

SELECT COUNT(ID)
FROM MY_TABLE
ORDER BY SUM(C_ONE), SUM(C_TWO), SUM(C_THREE)
LIMIT 2

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Well, you can unpivot and aggregate:

select c.col,
       (case when c.col = 'col1' then t.col1
             when c.col = 'col2' then t.col2
             when c.col = 'col3' then t.col3
        end) as cnt
from (select sum(col1) as col1, sum(col2) as col2, sum(col3) as col3
      from t
     ) t cross join
     (select 'col1' as col union all
      select 'col2' as col union all
      select 'col3' as col
     ) c
order by cnt desc
limit 2;

Upvotes: 1

Related Questions