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

#1

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 (DOT) com> wrote

Quote:

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?

#2

"rob" <rob (AT) dsdelft (DOT) nl> wrote

Quote:

Mark,

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

That should be (RANK end DENSE_RANK)

Quote:

Regards,
Rob

#3

mdejesus (AT) gmail (DOT) com (Mark Anthony) wrote in message news:<c95e8e39.0409021447.3982b936 (AT) posting (DOT) google.com>...

Quote:

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?

Since you have sales by store per month why not feed this into a query
for the store with max sales grouped by month.

select what_you_want from ( your query) group by store, month

HTH -- Mark D Powell --

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases.oracle comp.databases.oracle

Re: Find the store with the Max Sales for each month - 09-03-2004 , 04:09 AM

Re: Find the store with the Max Sales for each month - 09-03-2004 , 04:15 AM

Re: Find the store with the Max Sales for each month - 09-03-2004 , 08:21 AM