Re: need some SQL help

On Fri, 23 Dec 2005 18:45:55 GMT, howzat wrote:

>Hi all
>
>I'm having some trouble figuring this out so I'd appreciate if someone could
>point me in the right direction. (simplified schema appears below)
>
>I have an orders table which holds information about the type of order
>(phone, web, retail etc) and the amount of the order. The orders table is
>linked to a customers table via a customer_id column.
>
>I need to run a query which will return customers who match multiple order
>types and values e.g. all customers who spent more than 200 via mail order
>and more than 100 on the web
>
>The only way I've been able to do this is using subqueries, one for each
>order_type+amount condition. This works great on a small database but the
>live database will have about 30000 rows in the orders table and users will
>want to run a query on multiple order_type+amount conditions.

Hi David,

The following is untested since I don't have MySQL. It *should* work on all ANSI-compliant databases. It should also take no more than one single pass over the data in the table (if the optimizer in your database is worth it's salt, that is).

SELECT customer_id
FROM (SELECT customer_id, order_type

         FROM     orders
         WHERE    order_type IN (3, 4)
         GROUP BY customer_id, order_type
         HAVING ( order_type = 3 AND SUM(amount) > 100)
             OR ( order_type = 4 AND SUM(amount) > 200) ) AS derived

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)