473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to create a total

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?
Feb 20 '07 #1
1 1369
vijaydiwakar
579 512MB
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
Feb 22 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

0
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...
3
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...
3
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,...
2
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...
4
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...
2
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...
16
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
1
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
3
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...
4
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.