Count number of rows in each day grouped by another field

For the purpose of drawing an activity chart, how can we count number of rows for each type (distinct field value) in each day?

Consider a table with a date field and a field for each type:

CREATE TABLE TableName
    (`PK` int, `type` varchar(1), `timestamp` datetime)
;
    
INSERT INTO TableName
    (`PK`, `type`, `timestamp`)
VALUES
    (11, 'Q', '2013-01-04 22:23:56'),
    (7, 'A', '2013-01-03 22:23:41'),
    (8, 'C', '2013-01-04 22:23:42'),
    (10, 'Q', '2013-01-05 22:23:56'),
    (5, 'C', '2013-01-03 22:23:25'),
    (12, 'Q', '2013-01-05 22:23:57'),
    (6, 'Q', '2013-01-07 22:23:40'),
    (4, 'Q', '2013-01-02 22:23:23'),
    (9, 'A', '2013-01-05 22:23:55'),
    (1, 'A', '2013-01-08 21:29:38'),
    (2, 'Q', '2013-01-02 21:31:59'),
    (3, 'C', '2013-01-04 21:32:22')
;

For example output can be (last field is the count of rows with that type and in that day):

'Q', 2013-01-04, 1
'C', 2013-01-04, 2
'A', 2013-01-03, 1
'C', 2013-01-03, 2
and so on...

Answer

select `type`, date(`timestamp`) as the_date, count(*) as counter
from MyTable
group by `type`, date(`timestamp`)

Advertisement

Answer