3

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.

Steve Ross
  • 4,134
  • 1
  • 28
  • 40

1 Answers1

6

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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • SELECT download_date, image_file_id, credits from downloads GROUP BY download_date, image_file_id WITH ROLLUP – Steve Ross Oct 11 '10 at 20:22