Re: Oracle Query Help

>> This gives me a total for all weeks. I need it broken up by weeks.
<<

Oracle is supposed to have a CASE expression now, so you are not stuck with that awful, unreadable, proprietary DECODE(). You will have already created a Calendar table that has the calendar dates and their meanings in it; one of the columns should be week number.

SELECT P1.person_id, C1.week_nbr,

       SUM(CASE product WHEN 'a' THEN quantity ELSE 0 END) AS producta,

       SUM(CASE product WHEN 'b' THEN quantity ELSE 0 END) AS productb,

       SUM(CASE product WHEN 'c' THEN quantity ELSE 0 END) AS productc   FROM Persons AS P1,

       Calendar AS C1
 WHERE P1.purchase_date = C1.cal_date
 GROUP BY P1.person_id, C1.week_nbr;

In the future, you should post DDL, so people do not have to guess about the columns, table names, constraints, keys, NULLs, DRI, etc, etc. etc. that is required to help you. Do you work from specs like this when people are paying you? No, of course not; so why do you want other people to wrok from them for free?

If you are not familar with the use of auxillary tables, I have a few articles on them posted on the Web tha tyou can search for. Received on Wed Jun 20 2001 - 20:33:14 CEST