Re: Urgent: Help with grouping SQL!!

In article <95qr2m$oh3$1_at_nnrp1.deja.com>,   Billy Collins <queerczar_at_unforgettable.com> wrote:
> I have a SALE table which stores the information about each PRODUCT
> that is sold by the company. Of course, now there is is a date of the
> sale as well. Now, I need to report this information in the following
> manner:
>
> Sales during the week beginning Feb 5
>
> Mon Tue Wed Thu Fri
> 05/02 06/02 07/02 08/02 09/02
>
> PRODUCT 1 23 34 67 22 89
> PRODUCT 2 5 10 20 6 25
> ...
>
> What SQL can I write? My tables look like this:
>
> PRODUCT
> Product Id, Product name
>
> SALE
> Product Id, Date of Sale, Quantity of Sale
>
> I would appreciate any help!
>
> Thanks,
> Billy
>
> Sent via Deja.com
> http://www.deja.com/
>

Hi,

Don't know how general a problem you have to solve, but here is a select which solves the specific select of the dates between 05.02 and 09.02:

select p.product_name,

       sum(decode(trunc(s.date_of_sale),to_date('5-feb-2001','dd-mon- yyyy'),s.quantity_of_sale,0)) sale0502,

       sum(decode(trunc(s.date_of_sale),to_date('6-feb-2001','dd-mon- yyyy'),s.quantity_of_sale,0)) sale0602,

       sum(decode(trunc(s.date_of_sale),to_date('7-feb-2001','dd-mon- yyyy'),s.quantity_of_sale,0)) sale0702,

       sum(decode(trunc(s.date_of_sale),to_date('8-feb-2001','dd-mon- yyyy'),s.quantity_of_sale,0)) sale0802,

       sum(decode(trunc(s.date_of_sale),to_date('9-feb-2001','dd-mon- yyyy'),s.quantity_of_sale,0)) sale0902
from product p, sale s
where p.product_id = s.product_id
group by p.product_name
;

The "trick" is to sum either the actual quantity or 0 for each sale, each day.

Hope this helps.

Michael Ringbo

Sent via Deja.com
http://www.deja.com/ Received on Wed Feb 07 2001 - 03:49:31 CST