Re: Newbie : Group by price SQL

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Wed, 30 Jul 2003 00:33:44 +0100
Message-ID: <un0eiv49do0uvpv37jrdibp3tnm3csfd92@4ax.com>

On 29 Jul 2003 15:16:38 -0700, richmarin_at_earthlink.net (Rich) wrote:

>I need to create a daily report that shows management sales group by
>price. I wanted to know if it is possible to use a single SQL
>statement in order to do this. The report will look something like
>this :
>
> Column A Column B Column C Total
>of A, B, C
>
> Price < $10 Price > $11 to < $20 Price > $21
>
>Customer A $1,000.00 $500.00 $20.00
>$1,520.00

SELECT customer,

       SUM(CASE 
            WHEN price < 10 
            THEN price 
            ELSE 0 
           END) ColumnA,
       SUM(CASE 
            WHEN price > 11 AND Price < 20 
            THEN price 
            ELSE 0
           END) ColumnB,
       SUM(CASE 
            WHEN price > 21
            THEN price 
            ELSE 0
           END) ColumnC
       SUM(price) TotalPrice

FROM whatever
GROUP BY customer

 Although clearly you have discontinuities in your price ranges, and your value for ColumnC in the example above is impossible.

-- 
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Received on Tue Jul 29 2003 - 18:33:44 CDT