Re: SQL query help?

John McGough wrote:

>SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0)
>
>Work:-
>+---+-------+--------+---------+----------+
> | ID | JobID | UserID | Finished | Comment |
>+---+-------+--------+---------+----------+
> | 1 | 1 | user1 | 0 | ... |
> | 2 | 1 | user1 | 1 | ... |
> | 3 | 2 | user2 | 0 | ... |
> | 4 | 3 | user1 | 0 | ... |
> | 5 | 2 | user2 | 0 | ... |
> | 6 | 2 | user1 | 1 | ... |
> | 7 | 3 | user1 | 0 | ... |
>+---+-------+--------+---------+----------+
>
>All I want it to do is return the number of unfinished jobs for a specific
>user.
>
>In this example it would return 1 because job number 3 is not finished and
>user1 was the last person working on it.
>
>but I keep getting MySQL error #1111 - Invalid use of group function
>
>
John,

I may be missing something but how about

SELECT count(id) AS unfinished
FROM work
WHERE userid = 'user1'
AND finished = 0
GROUP BY jobid;

--
Kind Regards,
Keith