I have this statement:
SELECT product, decode(region, '501', 'Northeast', '502', 'Midwest', '503', 'West', '504', 'South', '505', 'Southeast', '506', 'MidAtlantic', '520', 'Molecular', '901', 'Ind East', '902', 'Ind West', 'Unknown'), sum(closeddollars)
FROM SMNDBA.Funnel
where (region <> '500') and (region <> '903') and
(closeddollars > 0) and (hiddenfiscalyear = '2007')
GROUP BY product, region
ORDER BY 1, 3 DESC
Which produces a list that looks like this:
PRODUCT REGION SUM(CLOSEDDOLLARS)
Product 1 Southeast 432504
Product 1 West 289464
Product 1 South 199944
Product 1 Midwest 181000
Product 1 Northeast 157068
Product 1 MidAtlantic 106000
Product 2 Ind West 92500
Product 2 Midwest 45000
Product 2 Northeast 18500
Product 3 MidAtlantic 519467
Product 3 Midwest 203302
Product 3 West 190000
Product 3 Southeast 170500
Product 3 Northeast 41500
Product 3 South 20112
Product 4 West 15000
Product 4 South 6800
Product 4 Southeast 3000
Product 4 Northeast 1200
Product 5 Molecular 728840
Product 6 Molecular 1534000
Product 7 Molecular 25500
I want to have the list of Products sorted by the overall dollars sold for each product while still retaining the amount of each product sold by each region. How do I get another total column for each product?
1 1369
I have this statement:
SELECT product, decode(region, '501', 'Northeast', '502', 'Midwest', '503', 'West', '504', 'South', '505', 'Southeast', '506', 'MidAtlantic', '520', 'Molecular', '901', 'Ind East', '902', 'Ind West', 'Unknown'), sum(closeddollars)
FROM SMNDBA.Funnel
where (region <> '500') and (region <> '903') and
(closeddollars > 0) and (hiddenfiscalyear = '2007')
GROUP BY product, region
ORDER BY 1, 3 DESC
Which produces a list that looks like this:
PRODUCT REGION SUM(CLOSEDDOLLARS)
Product 1 Southeast 432504
Product 1 West 289464
Product 1 South 199944
Product 1 Midwest 181000
Product 1 Northeast 157068
Product 1 MidAtlantic 106000
Product 2 Ind West 92500
Product 2 Midwest 45000
Product 2 Northeast 18500
Product 3 MidAtlantic 519467
Product 3 Midwest 203302
Product 3 West 190000
Product 3 Southeast 170500
Product 3 Northeast 41500
Product 3 South 20112
Product 4 West 15000
Product 4 South 6800
Product 4 Southeast 3000
Product 4 Northeast 1200
Product 5 Molecular 728840
Product 6 Molecular 1534000
Product 7 Molecular 25500
I want to have the list of Products sorted by the overall dollars sold for each product while still retaining the amount of each product sold by each region. How do I get another total column for each product?
Try to use Group by rollup command in oracle9i
Sign in to post your reply or Sign up for a free account.
Similar topics
by: unixman |
last post by:
As usual, it is 2:00am, and I'm pulling my hair out, finally resorting to
posting in the newsgroups for help. :)
Simple problem, in theory.
Given table "map":
CREATE TABLE map (
entry_id...
|
by: Impakt |
last post by:
Hi all...
I'm making a database which is a survey on coastal safety.
One of the questions asked is what activities the respondant
uses the coast for...I have the following:
Fishing
Surfing...
|
by: pw |
last post by:
Hi,
I am having a mental block trying to figure out how to code this.
Two tables:
"tblQuestions" (fields = quesnum, questype, question)
"tblAnswers" (fields = clientnum, quesnum, questype,...
|
by: mhodkin |
last post by:
I created a query in which I have grouped data by City. I wish to
calculate the percent of each value, e.g. City/(Total count of all
Cities), in tbe next column of the query. I can't seem to...
|
by: Scott Cameron |
last post by:
Here is what I have. I have a field that lists a product cost and another
field that lists how many times that particular product has been purchased
(so I would have a record that says I bought 25...
|
by: shanfitzpatrick |
last post by:
Ach, I'm in a bind...i can't get one of my queries to work properly.
And i'm certain it's because I don't know how to write the expression
correctly...
I am creating a database that enters items...
|
by: Gandalf186 |
last post by:
I need to create a query that produces running totals for every group within my table for example i wish to see: -
Group A
1
5
9
15
Group B
|
by: nick.leggin |
last post by:
I have a recordset that includes the following data
Location
Territory
Sales
Total Sales
% of total sales
A location can have multiple territories, and each territory has
different sales
|
by: Richard Hollenbeck |
last post by:
I hope this isn't too confusing.
The following query runs pretty fast by itself, but when I want to use it in
a report (pasted below the query), it takes at least fifteen seconds to run!
Then I...
|
by: lorirobn |
last post by:
Hi,
I have a report displaying items that are missing from a room. I
created 2 queries, the first getting the items IN the room, and the
second being an "unmatched" query that references the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |