Mysql group by day in a date_time field

Question

Is it ever possible to have a query that selects rows grouped by the day or month from a table that has a date_time filed holding full date_time value?

enter image description here

I need to get the sum of sr13, r4, huawei for each day. Any idea of how I can do it would be heartfully appreciated!

do you have any filters to restrict data to a month? If you group by day then you might get rows from different months for same day. — rs.
– rs., Commented Mar 12, 2013 at 5:18
$counter_result = $this->db->query("select date_time, sum(r4) as sum_r4, sum(sr13) as sum_sr13, sum(huawei) as sum_huawei from cdrcount where date_time like '2013-03-%' group by date_time"); — deviloper
– deviloper, Commented Mar 12, 2013 at 5:24

John Woo · Accepted Answer · 2013-03-12 05:21:26Z

2

You can use DATE() to extract the date from the value,

SELECT  DATE(date_time) datetime,
        SUM(sr13) total_sr13,
        SUM(r4) total_4
FROM    TableName
GROUP   BY DATE(date_time)

answered Mar 12, 2013 at 5:21

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

3 Comments

deviloper Over a year ago

What if I want to group by month.. to get the sum of the three(r4,sr13,huawei) for each month?

deviloper Over a year ago

Bingo.. I got it..: GROUP BY month(date_time);

John Woo Over a year ago

you can use either MONTH() or MONTHNAME() together with YEAR(), ex SELECT YEAR(date_time), MONTHNAME(date_time),.... GROUP BY YEAR(date_time), MONTHNAME(date_time)

Collectives™ on Stack Overflow

Mysql group by day in a date_time field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related