CODEWITHSUNDEEP
mysqlsqldatetime
jnbdz
jnbdz

Reputation: 4383

Is it possible to sum the result of a column based on the date being group by day?

Here I have a sample table:

 id |    datetime         | views
 --------------------------------
 1  | 2014-02-27 21:00:00 | 200
 65 | 2014-02-08 05:00:00 | 1
 65 | 2014-02-08 10:00:00 | 3
 65 | 2014-02-08 17:00:00 | 1
 65 | 2014-02-08 20:00:00 | 1
 65 | 2014-02-08 21:00:00 | 1
 65 | 2014-02-09 04:00:00 | 2
 65 | 2014-02-09 05:00:00 | 1
 65 | 2014-02-09 06:00:00 | 3
 65 | 2014-02-09 07:00:00 | 1
 65 | 2014-02-09 09:00:00 | 1
 65 | 2014-02-09 10:00:00 | 2
 65 | 2014-02-09 13:00:00 | 1
 70 | 2014-02-09 14:00:00 | 3
 70 | 2014-02-09 15:00:00 | 2

I am trying to get the views of a particular id per day and not per hour (this the setup of this table). What is the best way at it?

Upvotes: 0

Views: 32

Answers (3)

Nebojsa Susic
Nebojsa Susic

Reputation: 1260

SELECT sum(views) as vievs, DATE(datetime) as date
FROM table
GROUP BY DATE(datetime);

Upvotes: 0

VMai
VMai

Reputation: 10336

You can use the DATE function to get the date part of your datetime value:

SELECT
     id,
     DATE(datetime) AS mydate,
     SUM(views) AS total
FROM
     yourtable
GROUP BY
     id,
     DATE(datetime);

Upvotes: 2

potashin
potashin

Reputation: 44581

SELECT `id`
     , DATE(`datetime`)
     , SUM(`views`)
FROM `table`
GROUP BY `id`
        , DATE(`datetime`)

Upvotes: 2

Related Questions