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

User · Answer

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

User · Answer

Yes  you can use GROUP BY  SELECT time         activities         COUNT    FROM table GROUP BY time  activities

User · Answer

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

User · Answer

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

[sql] Count number of occurrences for each unique value

Examples related to sql

Examples related to count