Search code examples
sqlmysqlsubtotal

Forming SQL Query for Detail and Subtotals (MySQL)

I have a common SQL task but can't find a clear path to the solution. I have a table, downloads with the columns, download_date, image_file_id, credits. What I want at the end is the detail, plus a subtotal of credits for the day at the end of each day. E.g.

2010-10-06 123456 5
2010-10-06 234567 20
                  25
2010-10-07 234678 15
etc.

I can use SUM() and GROUP BY to get the daily subtotals, but would love a convenient way to get a result set that contained both in the proper order so I don't have to scan the data again in the client code to arrange the subtotals with the detail rows.

Solution

  • You will be able to accomplish this using the MySQL keyword ROLLUP:

    SELECT download_date, image_file_id, credits 
    GROUP BY download_date, image_file_id WITH ROLLUP

    or something similar. For more details see the MySQL manual.