CODEWITHSUNDEEP
mysqlsql
Astrid
Astrid

Reputation: 1312

Mysql query extracting date + group by day

Simple question: Why does the following query not output perday?

SELECT FROM_UNIXTIME(`date`,"%Y-%m-%d") AS `perday`, COUNT(*) AS `count` 
FROM `data` 
WHERE `group` = 1
GROUP BY `perday`

Count gets outputted correctly, but perday stays empty. The data table is like:

 |   id   |   group   |          date          |
------------------------------------------------
 |   1    |     1     |  2013-04-13 06:01:02   |
 |   2    |     1     |  2013-04-13 14:24:18   |
 |   3    |     2     |  2012-01-21 21:33:03   |
              Ect.

Thanks!

EDIT:

Expected output:

 |   perday   |
 --------------
 | 2013-04-13 |
 | 2012-01-21 |

Upvotes: 3

Views: 4596

Answers (3)

Deval Shah
Deval Shah

Reputation: 1094

MySQL DATE() takes the DATE part out from a DATETIME expression.As your Expected output this query is fine.

SELECT
  DATE(`date`) AS `perday`,
  COUNT(*) AS `count`
FROM `data`
GROUP BY `perday`

Upvotes: 0

John Woo
John Woo

Reputation: 263733

remove WHERE clause,

SELECT  FROM_UNIXTIME(date,'%Y-%m-%d') AS perday, 
        SUM(`group` = 1)  AS `count` 
FROM    data 
GROUP   BY FROM_UNIXTIME(date,'%Y-%m-%d')

if date is formatted as 2013-04-13 06:01:02, then why use FROM_UNIXTIME? Isn't it DATE_FORMAT instead?

SELECT  DATE_FORMAT(date, '%Y-%m-%d') AS perday, 
        SUM(`group` = 1)  AS `count` 
FROM    data 
GROUP   BY DATE(date)
ORDER   BY date

This will display all available dates in the table.

But if you want the selected group only,

SELECT  DATE_FORMAT(date, '%Y-%m-%d') AS perday, 
        COUNT(*)  AS `count` 
FROM    data 
WHERE   `group` = 1
GROUP   BY DATE(date)
ORDER   BY date

Upvotes: 6

echo_Me
echo_Me

Reputation: 37233

you may looking for this

    SELECT DATE_FORMAT(`date`,"%Y-%m-%d") AS `perday`, COUNT(*) AS `count` 
    FROM `data` 
    WHERE `group` = 1
    GROUP BY `perday`

DEMO HERE

Upvotes: 0

Related Questions