Re: Find the store with the Max Sales for each month

Mark,

Take a look at "SQL for Analysis" chapter 17 in Oracle8i Data Warehousing Guide (CUBE and ROLLUP).

Regards,
Rob

"Mark Anthony" <mdejesus_at_gmail.com> wrote in message news:c95e8e39.0409021447.3982b936_at_posting.google.com...
> ok so first i will give write the question out, my code, the db
> structure, then what is going wrong.
>
>
> Write a report to return the top store in terms of Sales Dollars for
> each month sales have been posted during 1998. Result set should only
> include: Month, Store Number, Store Name, State, & Total Sales
> Dollars. Sort the results by Month.
>
> So i am working with 2 tables for this one and they are as follows:
> Merchandise and daily_sales. merchandise and daily_sales are
> connected through a FK.
>
> merchandise:
> ean_no //main key
> title_txt //title of merch
> merch_fmt_cd //format code
> merch_type_cd //type code
> unit_price_amt//price
> subject_cd //subject
> release_dt //release date
> merch_desc //description
>
> daily_sales:
> store_id //store_id
> sale_dt //date of sale
> ean_no //mainkey fk
> sale_qty //number of items sold that day
>
> i can only use one statement to do this, no PL/SQL, no multiple
> statements
>
> MY QUERY:
>
> SELECT to_char(d1.sale_dt,'YYYY') year, to_char(d1.sale_dt,'MM')
> month, d1.store_id storeid, SUM(d1.sale_qty*m1.unit_price_amt) total
> FROM merchandise m1, daily_sales d1,
> (
> SELECT to_char(d2.sale_dt,'YYYY') year, to_char(d2.sale_dt,'MM')
> month,
> d2.store_id storeid, SUM(d2.sale_qty*m2.unit_price_amt) total
> FROM merchandise m2, daily_sales d2
> WHERE d2.ean_no=m2.ean_no
> GROUP BY to_char(d2.sale_dt,'YYYY'), to_char(d2.sale_dt,'MM'),
> d2.store_id
> ) sub
> WHERE to_char(d1.sale_dt,'YYYY')=sub.year AND
> to_char(d1.sale_dt,'MM')=sub.month AND
> d1.ean_no=m1.ean_no AND d1.store_id=sub.storeid
> GROUP BY to_char(d1.sale_dt,'YYYY'), to_char(d1.sale_dt,'MM'),
> d1.store_id
> HAVING SUM(d1.sale_qty*m1.unit_price_amt)=MAX(sub.total)
> ORDER BY to_char(d1.sale_dt,'YYYY'), to_char(d1.sale_dt,'MM')
>
> Currently this is returning sales for each store, each month. I
> thought the Max(sub.total) would retrieve just the max values, but
> apparently it isn't. I am getting more than i should. Any ideas?
Received on Fri Sep 03 2004 - 11:09:27 CEST