SQL - Sum Top N values in each row

Question

This is my SQL table.

+-------+------+------+------+------+
| name  | q1   | q2   | q3   | q4   |
+-------+------+------+------+------+
| Alex  |    5 |    4 |   10 |    7 |
| Brown |    7 |    6 |    4 |    1 |
| Chris |   10 |   10 |    9 |   10 |
| Dave  |    8 |    4 |    6 |    0 |
+-------+------+------+------+------+

I'd like to sum the top 2 scores from each user in my SQL query above.

For example, the top 2 scores of Alex are 10 and 7, and so the sum is 10 + 7 = 17

I have tried the following query:

SELECT NewStudents.name, SUM(q1+q2+q3+q4) FROM NewStudents 
GROUP BY NewStudents.name;

To sum all q1, q2, q3, q4 but this query sums all q1 to q4, not the top 2 scores among q1 to q4.

How can I construct the statement that I want to do in mySQL?

Your data structure makes what you want very difficult to accomplish, since sql is optimized to work across records, not within one. You should really change your data structure. — Shadow, Oct 28 '18 at 23:26
Consider revising your design. A database table is not a spreadsheet — Strawberry, Oct 28 '18 at 23:46

dwir182 · Answer 1 · 2018-10-29T04:24:25.547

As stated in @Shadow in comment.. Your database need restructure again.. Because that's not database work.. You can restructure and make design like this..

+-------+----+--------+
| name  |  q | point  |
+-------+----+--------+
| Alex  |  1 |      5 |
| Alex  |  2 |      4 |
| Alex  |  3 |     10 |
| Alex  |  4 |      7 |
| Brown |  1 |      7 |
| Brown |  2 |      6 |
| Brown |  3 |      4 |
| Brown |  4 |      1 |
| Chris |  1 |     10 |
| Chris |  2 |     10 |
| Chris |  3 |      9 |
| Chris |  4 |     10 |
| Dave  |  1 |      8 |
| Dave  |  2 |      4 |
| Dave  |  3 |      6 |
| Dave  |  4 |      0 |
+-------+----+--------+

And for the query you can do like this:

select
     name, sum(point)
from(
     select 
          name, q, point,
          ROW_NUMBER() OVER (PARTITION BY name ORDER BY point DESC) as ranked
     from newstudents) rankedSD
where
     ranked in (1,2)
group by 
     name

You can check the demo here:

Demo<>Fiddle

Edit : You can use Window Function. You can read Row_Number() Function

score 1 · Answer 2 · answered Oct 28 '18 at 23:50

1

A normalised design might look like this:

name q score
Alex  1  5
Alex 2  4
Alex 3 10
Alex 4  7

answered Oct 28 '18 at 23:50

Strawberry

33,750
13
40
57

Gordon Linoff · Answer 3 · 2018-10-29T13:55:25.910

In older versions of MySQL, you can use variables for this purpose:

select name, sum(q)
from (select nq.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from (select nq.*
            from ((select name, q1 as q from t
                  ) union all
                  (select name, q2 as q from t
                  ) union all
                  (select name, q3 as q from t
                  ) union all
                  (select name, q4 as q from t
                  )
                 ) nq
            order by name, q desc
           ) nq cross join
           (select @n := '', @rn := 0) params
      ) nq
where rn <= 2;

SQL - Sum Top N values in each row

3 Answers3