Re: need some SQL help

On Thu, 29 Dec 2005 17:02:12 GMT, howzat wrote:

>"Hugo Kornelis" <hugo_at_pe_NO_rFact.in_SPAM_fo> wrote in message >
>
>> 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
>> GROUP BY customer_id
>> HAVING COUNT(*) = 2
>>
>
>hugo, do you know how I'd modify your query to return only a count of the
>matching rows? I've tried various changes but I'm really just clutching at
>straws.
>

Hi howzat,

Happy new year! And sorry for the delayed reply - pressing family matters (a visit to Disneyland <g>) interfered.

I'm not sure what exactly you want to return. Some sample data and expected results would have helped here.

Anyway, assuming that you want a count of the number of customers that satisfy the condition outlined in your previous message, try if this works for you:

SELECT COUNT(*)
FROM (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 der
       GROUP BY customer_id) AS der2

Best, Hugo Received on Thu Jan 05 2006 - 23:37:23 CET