SQL Server Performance Forum – Threads Archive
How to aggregate this table
Hi guys I need to aggregate a table to three different levels but I need the results in a single table. Here is the sample data IndicatorName DHBName PHOName Practice PracticeName Numerator DenominatorABC SAM a PracticeA QW 22500 22.5
BNN SAM b PracticeB SSS 22500 22.5
dddd JONES c PracticeC FFFF 22500 45
ssss Alter d PracticeZ QW 22500 22.5
rrrr Sam a PracticeA FFFF 52500 60
ABC GINI b PracticeA ASDFF 45000 45
BNN Hoe c PracticeD Tahunanui Medical Centre 45000 15
Now I need to group this table first on the dhb level: Query used
SELECT IndicatorName, DHBName,sum( Num),sum( Den)
FROM DHBLevel
GROUP BY IndicatorName, DHBName Then group on PHO Level SELECT IndicatorName, DHBName, phoname,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM DHBLevel
GROUP BY IndicatorName, DHBName,phoname Then on Practice Level SELECT IndicatorName, DHBName, phoname,practicename,SUM(Num) AS Expr1, SUM(Den) AS Expr2
FROM DHBLevel
GROUP BY IndicatorName, DHBName,phoname,practicename. Now I need to see the aggregates in 1 single table only. How shall i do this?? Here is the create table script USE [PhoTest] CREATE TABLE [dbo].[performanceOctober](
[IndicatorName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[DHBName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PHOName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PracticeName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Numerator] [float] NULL, [Denominator] [float] NULL ) ON [PRIMARY]
why you want to do this in a single query?
obviously, you have to write 3 seperate queries for seperate level, else you have to this in SQL Server Analysis services
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Try using SQL COMMAND (NOT MDX) ‘CUBE’, search for ‘SELECT Examples’ in SQL Server 2005 Help.
Use ROLLUP or CUBE – check with Books Online to see exact format – I believe if you just use your 3rd query and add WITH ROLLUP you’ll get what you want. Add flight search to your own site for free
www.skyscanner.net
]]>