Find the store with the Max Sales for each month

From: Mark Anthony <mdejesus_at_gmail.com>
Date: 2 Sep 2004 15:47:18 -0700
Message-ID: <c95e8e39.0409021447.3982b936@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 Thu Sep 02 2004 - 17:47:18 CDT