Getting multiple maxes of summed values

Question

Consider this sample data:

+----------+------+--------+-----------------+
| username | qnum | qvalue | date            |
+----------+------+--------+-----------------+
| Linda    | 1    | 2      | 11/14/2017 7:25 |
+----------+------+--------+-----------------+
| Fred     | 1    | 1      | 11/23/2017 7:59 |
+----------+------+--------+-----------------+
| Brian    | 5    | 2      | 11/17/2017 7:25 |
+----------+------+--------+-----------------+
| Sandra   | 6    | 1      | 11/25/2017 7:26 |
+----------+------+--------+-----------------+
| Tom      | 6    | 1      | 11/22/2017 7:32 |
+----------+------+--------+-----------------+
| Paul     | 6    | 1      | 11/22/2017 7:36 |
+----------+------+--------+-----------------+
| Andrew   | 7    | 2      | 11/23/2017 7:37 |
+----------+------+--------+-----------------+
| Luke     | 3    | 1      | 11/23/2017 8:03 |
+----------+------+--------+-----------------+
| William  | 8    | 1      | 11/23/2017 8:03 |
+----------+------+--------+-----------------+
| Linda    | 9    | 2      | 11/15/2017 8:03 |
+----------+------+--------+-----------------+
| Brian    | 3    | 2      | 11/17/2017 8:04 |
+----------+------+--------+-----------------+
| Joan     | 9    | 1      | 11/23/2017 8:04 |
+----------+------+--------+-----------------+
| Chris    | 8    | 1      | 11/23/2017 8:04 |
+----------+------+--------+-----------------+
| Kim      | 8    | 1      | 11/15/2017 8:04 |
+----------+------+--------+-----------------+

I am attempting to get the person who has the highest sum of qvalue for last week. I am able to get this information with the following SQL but my problem is that if more than one user has the top score then it does not show both of their names because I am using the LIMIT function. Is there a way to use max and sum together to get the desired result? The desired result would be a result set with both Linda and Brian listed because last week they both had a sum score of 4 and were tied.

SELECT username, SUM(qvalue) AS score FROM trivia_scoreboard 
WHERE `date` >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND `date` < CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY
GROUP BY username
ORDER BY score DESC
LIMIT 1

What is the version of MariaDB? The latest one supports CTE. — Paul Spiegel, Nov 23 '17 at 19:09
@PaulSpiegel mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) — Tibblez, Nov 23 '17 at 19:18
See http://mysql.rjweb.org/doc.php/groupwise_max for efficient ways to get groupwise max with and without dups. — Rick James, Nov 24 '17 at 15:47

Barmar · Accepted Answer · 2017-11-23T19:18:19.190

4

You have to join that query with a query that gets everyone's total.

SELECT t1.*
FROM (
    SELECT username, SUM(qvalue) AS score FROM trivia_scoreboard 
    WHERE `date` >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND `date` < CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY
    GROUP BY username
) AS t1
JOIN (
    SELECT SUM(qvalue) AS score FROM trivia_scoreboard 
    WHERE `date` >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND `date` < CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY
    GROUP BY username
    ORDER BY score DESC
    LIMIT 1
) AS t2 ON t1.score = t2.score

DEMO

edited Nov 23 '17 at 19:18

answered Nov 23 '17 at 19:06

Barmar

741,623
53
500
612

There's no need for ORDER BY in the first subquery. However I would Just use a HAVING clause instead of the JOIN. – Paul Spiegel Nov 23 '17 at 19:17
Got rid of the `ORDER BY`, but not sure how to use `HAVING` for this. You can't say `HAVING score = MAX(score)` – Barmar Nov 23 '17 at 19:19
`having score = (original query without username with LIMIT 1)` - like in Gordons answer. But I'd be fine with both of the answers. – Paul Spiegel Nov 23 '17 at 19:22
@PaulSpiegel I don't think MySQL allows `LIMIT` when you use a subquery in a comparison like that. – Barmar Nov 23 '17 at 19:24
I've used that a lot even in the WHERE clause :-) .. http://www.sqlfiddle.com/#!9/89e7d/5 – Paul Spiegel Nov 23 '17 at 19:26
Ahh, it's only in `WHERE xxx IN (SELECT...)` that it's not allowed. https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql Well, I just get in the habit of using joins, they work better for lots of things in MySQL. – Barmar Nov 23 '17 at 19:29
Caution: The construct `FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ..` does becomes quite slow if the tables are big. – Rick James Nov 24 '17 at 15:48
@RickJames true, although notice that the subqueries being joined are the result of grouping, so they'll be much smaller than the original tables. And the second subquery returns just a single row, so it's not a big cross product. The only problem is if doing the grouping is expensive -- then it might be necessary to do that into a temporary table (necessary to make up for MySQL's lack of CTE). – Barmar Nov 24 '17 at 16:02
@Barmar - I wonder if the Optimizer is smart enough to realize that one subquery has only one row and do that one first. MySQL 8.0 and MariaDB 10.2 have CTEs; I wonder if their Optimizers are smart here. – Rick James Nov 24 '17 at 16:13
More impressive would be if the optimizer realizes that the subqueries are the same except for `ORDER BY` and `LIMIT`, so it does it once like a CTE and reuses the temporary table. – Barmar Nov 24 '17 at 18:02

Gordon Linoff · Answer 2 · 2017-11-23T19:41:07.603

3

You can also phrase this using having:

SELECT ts.username, SUM(ts.qvalue) AS score
FROM trivia_scoreboard fs
WHERE ts.`date` >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND 
      ts.`date` < CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY
GROUP BY username
HAVING score = (SELECT SUM(ts2.qvalue) as score
                FROM trivia_scoreboard ts2
                WHERE ts2.`date` >= CURDATE() - INTERVAL DAYOFWEEK(CURDATE())+6 DAY AND 
                      ts2.`date` < CURDATE() - INTERVAL DAYOFWEEK(CURDATE())-1 DAY
                GROUP BY username
                ORDER BY score DESC
                LIMIT 1
               );

The difference between this and the version using join is really a matter of taste. This might have slightly better performance, because of details about how aggregations scale on larger amounts of data.

edited Nov 23 '17 at 19:41

answered Nov 23 '17 at 19:17

Gordon Linoff

1,242,037
58
646
786

This answer also solves my problem, I accepted Barmar's answer only because he answered first. – Tibblez Nov 23 '17 at 19:24
@Tibblez . . . I upvoted Barmar's because it is just as correct. There may be some circumstances where one version would be preferred over the other. – Gordon Linoff Nov 23 '17 at 19:25
No need for `ORDER BY score DESC` at the end, since everyone has the same score (I mistakenly kept that in my original answer, too). – Barmar Nov 23 '17 at 19:30
I'd like to hope that our two queries would produce the same execution plan, but the ways of MySQL are mysterious. :) – Barmar Nov 23 '17 at 19:31
@Barmar . . . Actually, I think you are right in this case. – Gordon Linoff Nov 23 '17 at 19:40

Getting multiple maxes of summed values

2 Answers2