| SUM(SALES_AMT) assignment help [message #300669] |
Sun, 17 February 2008 12:52  |
jlbovo
Messages: 8
Registered: February 2008
|
Junior Member |
|
|
Hello everyone,
I am doing a assignment for class and I need a little help with it.
The question is " Determine the total combined sales for the products: diet cola, grape, and lime soda in the Southwest for all four quarters."
And this is my query:
SELECT P.PRODUCT_NAME "PRODUCT", SUM(S.SALES_AMT) "SALES"
FROM POP_PRODUCT P, POP_SALES S
WHERE P.PRODUCT_ID=S.PRODUCT_ID
AND S.PRODUCT_ID IN ('P1', 'P2', 'P3')
AND S.TIME_ID IN ('Q1', 'Q2', 'Q3', 'Q4')
AND S.LOCATION_ID='L4'
GROUP BY P.PRODUCT_NAME;
and then my output is:
PRODUCT SALES
------------ ----------
DIET COLA 355
GRAPE SODA 250
LIME SODA 170
So i need a little help figuring out how I get my query to add the numbers 355, 250, and 170 together. I'll continue to be working on this, and any help is APPERCIATED !!
Thanks again - Justin
|
|
|
|
|
|
| Re: SUM(SALES_AMT) assignment help [message #300671 is a reply to message #300669] |
Sun, 17 February 2008 13:36  |
jlbovo
Messages: 8
Registered: February 2008
|
Junior Member |
|
|
hello and thanks !!
I just learned about the rollup clause the other day and I must have looked over my notes. That does work so wonderfully.
This may have even helped my with my next question, because I seem to be getting the wrong figures again.
The other question I have now is: "Determine the total combined sales for orange soda in the locations Northeast and Midwest for the first and second quarters."
I can tell you right now that figures that I want to add up from my sales table in the locations and the quarter columns (25, 35, 30, 25)..
So i run this query (thanks for the ROLLUP info):
SELECT P.PRODUCT_NAME "PRODUCT", SUM(S.SALES_AMT) "COMBINED SALES"
FROM POP_PRODUCT P, POP_SALES S
WHERE P.PRODUCT_ID=S.PRODUCT_ID
AND S.PRODUCT_ID='P4'
AND S.TIME_ID IN ('Q1', 'Q2')
AND S.LOCATION_ID IN ('L1', 'L2')
GROUP BY ROLLUP(P.PRODUCT_NAME, S.SALES_AMT);
and my output looks like this:
PRODUCT COMBINED SALES
------------ --------------
ORANGE SODA 50
ORANGE SODA 30
ORANGE SODA 35
ORANGE SODA 115
115
I'm not sure why it dosen't look like:
PRODUCT COMBINED SALES
------------ --------------
ORANGE SODA 25
ORANGE SODA 35
ORANGE SODA 30
ORANGE SODA 25
115
...does it have something to do with UNIQUE clause i should include somewhere ??
Thanks again for all the help !! - Justin
|
|
|
|
|
|
| Re: SUM(SALES_AMT) assignment help [message #300676 is a reply to message #300669] |
Sun, 17 February 2008 14:25 |
jlbovo
Messages: 8
Registered: February 2008
|
Junior Member |
|
|
ok thanks ! does does produce what I was needing to see.
Let me see if i understadn this then about ROLLUP.
it adds the sums of sales_amt, based on the product stated in the group by clause in the end ?
thanks so much, you'e been helpful - justin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
