prakashchhetri prakashchhetri - 2 months ago 8
MySQL Question

Merge data on multiple rows in one

I have a table(MySQL) which stores the utilization of users. Now, what I want to do is, get the total utilization per day for each user. I can get data for each user, however, I am finding it really difficult to merge data from multiple rows for a single day.

Right now, the data I get is as :

id date download upload
1 2015-10-28 08:05:10 1 5
2 2015-10-28 10:25:15 2 5
3 2015-10-28 11:25:10 3 4
4 2015-10-29 11:25:10 8 5
5 2015-10-29 11:25:10 2 7
6 2015-10-29 11:25:10 1 3
7 2015-10-30 11:25:10 11 10
8 2015-10-30 11:25:10 4 5
9 2015-10-30 11:25:10 5 1
10 2015-10-30 11:25:10 10 1


But what I want it to appear like is :

id date download upload
1 2015-10-28 6 14
4 2015-10-29 11 15
7 2015-10-30 30 17

Answer

You can use the following query:

SELECT MIN(id) AS id, DATE(`date`) AS 'date', 
       SUM(download) AS download, SUM(upload) AS upload
FROM mytable
GROUP BY DATE(`date`) 

The query uses DATE function in order to extract the date value from the date field.

Demo here