Merge data on multiple rows in one

Question

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

Giorgos Betsos · Accepted Answer · 2016-09-10 07:24:23Z

1

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

answered Sep 10, 2016 at 7:24

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

prakashchhetri Over a year ago

Can I add a WHERE clause here, where username = $username

Giorgos Betsos Over a year ago

@prakashchhetri Yes, you can add a WHERE clause.

prakashchhetri Over a year ago

Great. It worked. Thanks a lot. Now I am just trying to solve it in a standard codeigniter way. Any ideas?

Giorgos Betsos Over a year ago

@prakashchhetri I'm afraid I can't help you with codeigniter, since I have no knowledge of it.

prakashchhetri Over a year ago

Ok. What about getting data between two dates only.

|

Sougata Bose · Accepted Answer · 2016-09-10 07:24:27Z

0

You can try this -

select id, date(`date`) `date`, sum(download) `download`, sum(upload) `upload`
from your_table
group by date(`date`)
order by `date`

It will sum all the downloads & uploads grouping by the date.

answered Sep 10, 2016 at 7:24

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Comments

ZeroCool · Accepted Answer · 2016-09-10 07:48:13Z

0

you need to a simple sql query lik this:

select max(user_id),op_date,sum(download_count) from test group by op_date;

data: 1 1 09-SEP-16 2
2 1 09-SEP-16 1
5 1 09-SEP-16 3
4 1 10-SEP-16 2
3 1 10-SEP-16 4

filtered data:

1 09-SEP-16 6
1 10-SEP-16 6

edited Sep 10, 2016 at 7:48

answered Sep 10, 2016 at 7:38

ZeroCool

12 bronze badges

Comments

Priyanka Patil · Accepted Answer · 2016-09-10 07:51:28Z

0

Use group by command

select id,`date`,sum(download) as 'download',sum(upload) as 'upload' 
from ur_table 
group by date(`date`)

answered Sep 10, 2016 at 7:51

Priyanka Patil

177 bronze badges

Collectives™ on Stack Overflow

Merge data on multiple rows in one

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related