Message-ID: <422D2BD8.2070806@NarrowPathInc.com>
Date: Mon, 07 Mar 2005 23:36:40 -0500
From: Keith Worthington <KeithW@NarrowPathInc.com>
Reply-To: KeithW@NarrowPathInc.com
Organization: Narrow Path, Inc.
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: John McGough <goff@funkster.org.uk>
Subject: Re: SQL query help?
References: <393ddoF5pqrfaU1@individual.net>
In-Reply-To: <393ddoF5pqrfaU1@individual.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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