MySql Count group by help

[DarkKnight2011]

Hi Guys,

 

cant seem to work this one out (been one of those days), so any help would be great

 

Using latest version of mysql

 

table - jobs labour

fields - labour_id, job_id, engineer_id, date, hours_booked

 

information i want out of it - I want a query that gives me a list of engineer_id's and how many individual jobs they have booked time on

 

current query

 

$query = "SELECT engineer_id, COUNT(labour_id) FROM jobs_labour 
WHERE (date BETWEEN '2011-10-03' AND '2011-10-09')
GROUP BY engineer_id"

 

This gives me a total count of how many time bookings each engineer has booked for this week, but i need to know how many unique jobs have they booked time to

 

Thanks for your help

[AyKay47]

you can use COUNT(DISTINCT) here

 

$query = "SELECT engineer_id, COUNT(labour_id), COUNT(DISTINCT labour_id) FROM jobs_labour 
WHERE (date BETWEEN '2011-10-03' AND '2011-10-09')
GROUP BY engineer_id"

[DarkKnight2011]

great thanks,

 

Needs to be a distinct job_id's though, any ideas how to add that to the query without breaking it?

[DarkKnight2011]

ah its ok, i just realised i can change it to job_id

 

like i said, its been one of those days - head isnt working

 

Thanks for your help mate

[AyKay47]

ah its ok, i just realised i can change it to job_id

 

like i said, its been one of those days - head isnt working

 

Thanks for your help mate

i've had plenty of those days as well so I can relate.. glad I could help