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:
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.
Yes, you can use GROUP BY:
SELECT time,
activities,
COUNT(*)
FROM table
GROUP BY time, activities;
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.
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
select time, coalesce(count(case when activities = 3 then 1 end), 0) as count
from MyTable
group by time
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.
Source: Stackoverflow.com