Thread: SQL query help?

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 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 workWHERE userid = 'user1'  AND finished = 0GROUP BY jobid;

-- 
Kind Regards,
Keith

On Mon, Mar 07, 2005 at 04:22:15PM -0000, John McGough wrote:

> +---+-------+--------+---------+----------+
>  | ID | JobID | UserID | Finished  | Comment |
> +---+-------+--------+---------+----------+

This table output doesn't look like PostgreSQL's usual format.

> but I keep getting MySQL error #1111 - Invalid use of group function

And this error definitely doesn't look like something from PostgreSQL.
Are you sure you're asking the right list?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/