Count number of occurrences for each unique value

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.

score 63 · Accepted Answer · edited Jan 06 '21 at 23:13

63

Yes, you can use GROUP BY:

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

edited Jan 06 '21 at 23:13

informatik01

16,038
10
74
104

answered Nov 05 '12 at 16:19

Jamey Sharp

8,363
2
29
42

Thanks! This seems the simplest and most functional solution :) – James Elder Nov 05 '12 at 16:36
1

@JamesElder, happy to help! Would you consider marking the answer "accepted"? :-) – Jamey Sharp Nov 05 '12 at 16:47
I don't think this solution would show the value "0" for the 13:30 group – Giacomo Nov 28 '20 at 14:25

score 14 · Answer 2 · edited Feb 11 '15 at 13:25

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.

This was very useful, as I wanted to have the result of counting different values on each result. Thanks! — Peter, Feb 17 '14 at 15:14

score 5 · Answer 3 · answered Nov 05 '12 at 16:18

5

If i am understanding your question, would this work? (you will have to replace with your actual column and table names)

SELECT time_col, COUNT(time_col) As Count
FROM time_table
GROUP BY time_col
WHERE activity_col = 3

answered Nov 05 '12 at 16:18

Modika

6,192
8
36
44

score 0 · Answer 4 · edited Apr 29 '18 at 04:58

0

You should change the query to:

SELECT time_col, COUNT(time_col) As Count
FROM time_table
WHERE activity_col = 3
GROUP BY time_col

This vl works correctly.

edited Apr 29 '18 at 04:58

bruntime

371
2
13

answered Mar 09 '17 at 05:39

geetha

31
1
5

Count number of occurrences for each unique value

4 Answers4

Linked