CODEWITHSUNDEEP
mysqlsqlgroup-by
Simone Giusti
Simone Giusti

Reputation: 323

MySql Query with Select Count and Group

I'm developing a php page and I should make a MySql query for extract some data from a table.

My table is like this:

Date (datetime) | Code (text)

2016-02-25 11:47:01 | FOO
2016-02-25 17:25:03 | BAR
2016-02-24 10:42:04 | BAR
2016-02-24 19:45:05 | BAR
2016-01-17 13:12:06 | BAR
2016-02-23 22:36:07 | XXX
2016-02-20 03:25:08 | YYY

I would like to write a query to obtain only "BAR" with count of occurrency by day, something like:

2016-02-25 | 1
2016-02-24 | 2
2016-01-17 | 1

The only part of query that I'm able to write is SELECT [...] WHERE Code="BAR" ;-)

Upvotes: 0

Views: 28

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

it will work for you

    select DATE(`datetime`)as date,count(Date)
    from table_name
WHERE Code = 'BAR'
    group by DATE(`datetime`)

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You have to GROUP BY the date part of the datetime field:

SELECT DATE(`datetime`), COUNT(*)
FROM mytable
WHERE Code = 'BAR'
GROUP BY DATE(`datetime`)

Upvotes: 2

Related Questions