Re: Challenging SQL request - Jeff Kilbride - com.mysql.lists.java

5 messages in com.mysql.lists.javaRe: Challenging SQL request

From	Sent On	Attachments
Donovan Walker	23 Jul 2002 02:34
Zart Colwing	23 Jul 2002 02:36
Zart Colwing	23 Jul 2002 04:29
Arthur Fuller	24 Jul 2002 05:05
Jeff Kilbride	25 Jul 2002 09:13

Subject:	Re: Challenging SQL request
From:	Jeff Kilbride (je...@kilbride.com)
Date:	07/25/2002 09:13:24 AM
List:	com.mysql.lists.java

Actually, you can use the "HAVING" part of the GROUP BY clause to do this:

client: id name

order: id client_id date

SELECT MAX(c.name), COUNT(o.id) AS numOrders FROM client c, order o WHERE o.date BETWEEN [date1] AND [date2] AND c.id = o.client_id GROUP BY o.client_id HAVING numOrders > 3

The "HAVING" is applied after the GROUP BY, so it's like being able to use a WHERE clause on the grouped information.

Thanks, --jeff

If you're talking MySQL here as opposed to "pure" SQL, then you can't AFAIK do it in one query: you need a temp table to store the counts in. then you simply query that table for counts > 3. In pure SQL you can do it in one statement since you can do a nested query.

hth, Arthur

Hi,

I've been ask once to write a SQL request that fulfill the following requirement, but as I miserably failed I'm asking the list for some help - it won't help my case, but it will enlighten me !

The requirement: "Write the SQL request that returns the clients who have more than 3 orders between two dates." The database schema was not given but I can infer that we have an academically organized database with at least a "client" table with an "id" and "name" column and a "order" table with a "date" and "client_id" columns.

-----

Your suggestions are very much welcomed.

ZartC

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail java...@lists.mysql.com instead.