Rollup/Cube with % of Total - 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

Transact-SQL (2000)

Rollup/Cube with % of Total

Author

Topic

msharps
Starting Member

2 Posts

Posted - 2004-11-10 : 22:01:49

Hello all -

I am developing a query with a cube "rollup". I also need to provide the "percent of total" for each grouped item. See the Northwind example below where the red rows is what I'm after.


EMPLOYEE | CATEGORY       | SALES
--------------------------------------
Davolio  | Meat/Poultry   | 16346.0700
Davolio  | Seafood        | 24947.0500
Davolio  | Total          | 41293.1200
Davolio  | Meat/Poultry % | .3959
Davolio  | Seafood %      | .6041
Fuller   | Meat/Poultry   | 32973.5100
Fuller   | Seafood        | 17561.7000
Fuller   | Total          | 50535.2100
Fuller   | Meat/Poultry % | .6525
Fuller   | Seafood %      | .3475
Total    | Meat/Poultry   | 49319.58
Total    | Seafood        | 42508.75
Total    | Total          | 91828.3300
Total    | Meat/Poultry % |.5371
Total    | Seafood %      | .4629

The query returns the total category sales for meat and seafood with a cube total on category and employee. I would like to have the percent of the cube total for each category with the group. For example, Meat/Poultry is 40% of the total category sales for employee Davolio.

Unfortunately, I am unable to utilize Analysis Services on this particular project.
I know I can accomplish this within the application code (asp) but I'm wondering if this is possible (or even recommended) using t-sql.

Any input is appreciated. Thanks.

Here's the sql for the query example:


USE Northwind
SELECT
	CASE WHEN GROUPING(e.LastName)=1 THEN 'Total' ELSE e.LastName END AS Employee,
	CASE WHEN GROUPING (c.CategoryName)=1 THEN 'Total' ELSE c.CategoryName END AS Category,
	SUM(od.UnitPrice * od.Quantity) AS Sales
FROM
	orders AS o
	INNER JOIN [order details] AS od ON (o.OrderID = od.OrderID)
	INNER JOIN employees AS e ON (o.EmployeeID = e.EmployeeID)
	INNER JOIN products AS p ON (od.ProductID = p.ProductID)
	INNER JOIN categories AS c ON (p.CategoryID = c.CategoryID)
WHERE
	c.CategoryID IN (6,8)
	AND
	e.EmployeeID IN (1,2)
GROUP BY
	e.LastName,
	c.CategoryName
WITH CUBE
ORDER BY
	GROUPING(e.LastName),
	e.LastName,
	GROUPING(c.CategoryName),
	c.CategoryName

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 22:03:40

Any reason why this can't be done in a report? It will be more efficient than changing the query.

msharps
Starting Member

2 Posts

Posted - 2004-11-11 : 00:01:08

Thanks Rob, that confirms what I had suspected but I thought I would post the question anyway just in case.

I have simplified the example above for the sake of illustration - the actual query is pivoted into a crosstab report and can have a variable number of pivot columns which makes the application code especially cumbersome to accomodate the "% of Total" requirement.

This will be fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources