J. Harwood J. Harwood - 6 days ago 6
SQL Question

SQL select top SUM grouped by week

How would I need to change the following SQLite3 query to get just the row with highest totals grouped by week:

SELECT
strftime('%W', date(p.match_date, 'unixepoch', 'localtime')) AS week_number,
sum(red_cards + yellow_cards) AS cards, user_id
FROM user_records
GROUP BY week_number, user_id
ORDER BY week_number, cards DESC


The above query returns the following results:

week_number - cards - user_id
44 5 1
44 1 2
45 2 2
45 1 1


I'm trying to show just the top row for each week:

week_number - cards - user_id
44 5 1
45 2 2


Is there some trick to adjust the query to drop the unnecessary extra rows?

Answer

Try using NOT EXISTS() :

SELECT s.* FROM ( 
    SELECT 
        strftime('%W', date(p.match_date, 'unixepoch', 'localtime')) AS week_number,
        sum(red_cards + yellow_cards) AS cards, user_id
    FROM user_records
    GROUP BY week_number, user_id) s
WHERE NOT EXISTS(SELECT 1 FROM user_records p
                 WHERE strftime('%W', date(p.match_date, 'unixepoch', 'localtime')) = s.week_number
                 GROUP BY p.user_id
                 HAVING SUM(red_cards + yellow_cards) > s.cards)
Comments