Home » Other » Training & Certification » SUM(SALES_AMT) assignment help (oracle 9i)
SUM(SALES_AMT) assignment help [message #300669] Sun, 17 February 2008 12:52 Go to next message
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 #300670 is a reply to message #300669] Sun, 17 February 2008 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 ways:
- Using SQL*Plus COMPUTE command
- Using ROLLUP

Regards
Michel
Re: SUM(SALES_AMT) assignment help [message #300671 is a reply to message #300669] Sun, 17 February 2008 13:36 Go to previous messageGo to next message
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 #300673 is a reply to message #300671] Sun, 17 February 2008 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You rollup on 2 columns so you have the sum for these 2 column one on rollup of product (column product NULL), one on rollup of sales_amt (product name set).
why do you rollup on sales_amt?

Regards
Michel
Re: SUM(SALES_AMT) assignment help [message #300676 is a reply to message #300669] Sun, 17 February 2008 14:25 Go to previous message
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
Previous Topic: Oracle forms and reports 10g
Next Topic: extents in tablespace
Goto Forum:
  


Current Time: Sun Dec 04 23:09:32 CST 2016

Total time taken to generate the page: 0.07760 seconds