MySql Query with Select Count and Group

Question

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" ;-)

Giorgos Betsos · Accepted Answer · 2016-03-04 15:37:09Z

2

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`)

answered Mar 4, 2016 at 15:37

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Comments

Ankit Agrawal · Accepted Answer · 2016-03-04 15:46:10Z

1

it will work for you

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

edited Mar 4, 2016 at 15:46

answered Mar 4, 2016 at 15:37

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

1 Comment

user1717259 Over a year ago

You need to trim off the time part to get grouping by date.

Collectives™ on Stack Overflow

MySql Query with Select Count and Group

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related