Rollup Subtotals Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rollup Subtotals Problem

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2004-12-14 : 12:02:02

I'm working on a report that uses grouping with rollup to summarize some data. My data is arranged very similarly to the original grouping/rollup example I found, so I'll use that sample data in my question.

See: http://aspnet.4guysfromrolla.com/articles/073003-1.aspx

In their example they are grouping products by categories. Each product has a unitprice, and the grouping/rollup function provides a subtotal of the unitprice data for each category.

So let's say in the Produce category we have Tofu (unitprice $23), dried apples (unitprice $53), and pears (unitprice $30). The automatically-generated subtotal for the produce category would then be $106. (Let's pretend only 1 unit of each product is in stock.)

I need to take this one step further. I need to fill a column with the percentage of the subtotal that each product represents. So the Tofu, at $23/unit, represents 21.7% of the total produce value of $106.

But getting this value would require somehow accessing the subtotal in that part of the grouping, and I have NO idea how to do that.

Any suggestions?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-14 : 12:19:21

can you do it after the rollup...in the presentation stage?

otherwise you are into some form of running totals...in which case there are several examples here....as indeed (if i remember properly) there are to this problem....search for percentage

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 12:56:06

consider the following:


select 
  a.Store, a.Product, a.Cost, (a.cost * 1.0 / b.TotalCost) as PctOfTotal
from 
  YourData a
inner join
  (select Store, sum(cost) as TotalCost from YourData group by Store) b
on 
 a.Store = b.Store

- Jeff

MrRay
Starting Member

28 Posts

Posted - 2004-12-14 : 13:33:44

quote:
Originally posted by AndrewMurphy

can you do it after the rollup...in the presentation stage?

otherwise you are into some form of running totals...in which case there are several examples here....as indeed (if i remember properly) there are to this problem....search for percentage

Andrew,

I can do it either in the SQL statement or during databinding (or before). The most important part of this report is showing the percentage of the subtotal that the item represents, so I'll change things around to get it if I have to.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 14:21:40

did you see and/or understand my post? It's the answer you are looking for.

- Jeff

MrRay
Starting Member

28 Posts

Posted - 2004-12-14 : 22:20:34

quote:
Originally posted by jsmith8858

did you see and/or understand my post? It's the answer you are looking for.

- Jeff

Hey Jeff,

I haven't had the chance to implement it yet. I'll be trying that first thing in the morning. I hadn't thought of using grouping within another SQL query with grouping. I'm a little fuzzy on how it will work (since I'm new to grouping) but I'll hit you up with a question or two if I'm stuck.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources