Count number of occurrences for each unique value [duplicate]

Question

Basically I have a table similar to this:

time.....activities.....length   13:00........3.............1   13:15........2.............2   13:00........3.............2   13:30........1.............1   13:45........2.............3   13:15........5.............1   13:45........1.............3   13:15........3.............1   13:45........3.............2   13:45........1.............1   13:15........3.............3   

A couple of notes:

• Activities can be between 1 and 5
• Length can be between 1 and 3

The query should return:

time........count   13:00.........2   13:15.........2   13:30.........0   13:45.........1   

Basically for each unique time I want a count of the number of rows where the activities value is 3.

So then I can say:

At 13:00 there were X amount of activity 3s. At 13:45 there were Y amount of activity 3s. 

Then I want a count for activity 1s,2s,4s and 5s. so I can plot the distribution for each unique time.

Answer 1

Yes, you can use GROUP BY:

SELECT time,        activities,        COUNT(*) FROM table GROUP BY time, activities; 

Answer 2

select time, coalesce(count(case when activities = 3 then 1 end), 0) as count from MyTable group by time 

SQL Fiddle Example

Output:

|  TIME | COUNT | ----------------- | 13:00 |     2 | | 13:15 |     2 | | 13:30 |     0 | | 13:45 |     1 | 

If you want to count all the activities in one query, you can do:

select time,      coalesce(count(case when activities = 1 then 1 end), 0) as count1,     coalesce(count(case when activities = 2 then 1 end), 0) as count2,     coalesce(count(case when activities = 3 then 1 end), 0) as count3,     coalesce(count(case when activities = 4 then 1 end), 0) as count4,     coalesce(count(case when activities = 5 then 1 end), 0) as count5 from MyTable group by time 

The advantage of this over grouping by activities, is that it will return a count of 0 even if there are no activites of that type for that time segment.

Of course, this will not return rows for time segments with no activities of any type. If you need that, you'll need to use a left join with table that lists all the possible time segments.