Friday 3 August 2012

Mysql - Grouping Row and Counting them

The problem:  I am printing out shifts done at certain venues.  And what I want to do is on some shifts rather than print out every shift; if the shift is done at the same time on the same date then these can be grouped together .  Also though I'll need to know how many shifts have been grouped together to refer to later in my code.

Question : can I group by 3 different columns.

Yes. and this gives me less row results - so if I can get a count of how many rows have been grouped then I'm on to a winner.

Question:  How Can i get a count of how many rows have been grouped together by GROUP BY in MySQL

By using count(*) as count in the select .  this gives me a column with how many times some rows have been grouped

Here's the original mysql call .


SELECT * FROM vfvi7_pf_time_tracking
WHERE vfvi7_pf_time_tracking.cdate >=1339974000
AND vfvi7_pf_time_tracking.cdate <=1340578800
AND vfvi7_pf_time_tracking.project_id =7


and the new mysql call is

SELECT *, count(*) as count
FROM vfvi7_pf_time_tracking
WHERE vfvi7_pf_time_tracking.cdate >=1339974000
AND vfvi7_pf_time_tracking.cdate <=1340578800
AND vfvi7_pf_time_tracking.project_id =7  GROUP BY vfvi7_pf_time_tracking.cdate, vfvi7_pf_time_tracking.start_time, vfvi7_pf_time_tracking.end_time

No comments: