Forming SQL Query for Detail and Subtotals (MySQL)

sql mysql subtotal
Steve Ross picture Steve Ross · Oct 11, 2010 · Viewed 8.2k times · Source

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.

Answer

Larry Lustig picture Larry Lustig · Oct 11, 2010

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.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More