Find the store with the Max Sales for each month
Date: 2 Sep 2004 15:47:18 -0700
Message-ID: <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)
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 - 00:47:18 CEST