How to aggregate this table - 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 2005 Forums

Transact-SQL (2005)

How to aggregate this table

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2007-01-21 : 20:11:02

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 Denominator
ABC 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]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-21 : 20:19:18

How will be the result looks like ?

by the way, you missed out the column Practice in the table script

mitasid
Yak Posting Veteran

51 Posts

Posted - 2007-01-21 : 20:24:25

Hi sorry i have missed that.
I want my table to look like following:

IndicatorName DHB PHO Practice DHBTotalNum DHbtotalDen PHOTotalNum PHOTOTALDen PracticeTotalNum PracticetotalDen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-21 : 20:33:52

try this


select p.IndicatorName, p.DHBName, p.phoname, p.practicename,
       DHBTotalNum      = d.Expr1,
       DHBTotalDen      = d.Expr2,
       PHOTotalNum      = h.Expr1,
       PHOTotalDen      = h.Expr2,
       PracticeTotalNum = p.Expr1,
       PracticeTotalDen = p.Expr2
from
(
    SELECT   IndicatorName, DHBName, phoname, practicename,
             SUM(Num) AS Expr1, SUM(Den) AS Expr2
    FROM     DHBLevel
    GROUP BY IndicatorName, DHBName,phoname,practicename
) p
inner join
(
    SELECT   IndicatorName, DHBName, phoname, SUM(Num) AS Expr1, SUM(Den) AS Expr2
    FROM     DHBLevel
    GROUP BY IndicatorName, DHBName, phoname
) h
on  p.IndicatorName = h.IndicatorName
and p.DHBName       = h.DHBName
and p.phoname       = h.phoname
inner join
(
    SELECT   IndicatorName, DHBName, sum( Num) as Expr1, sum( Den) as Expr2
    FROM     DHBLevel
    GROUP BY IndicatorName, DHBName
) d
on  p.IndicatorName = d.IndicatorName
and p.DHBName       = d.DHBName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 00:46:58

GROUP BY ... WITH ROLLUP
GROUP BY ... WITH CUBE

Peter Larsson
Helsingborg, Sweden

herothecat
Starting Member

19 Posts

Posted - 2007-01-23 : 20:24:32

SELECT
IndicatorName, DHBName, PHOName, PracticeName,
Numerator, Denominator,
sum(Numerator) OVER (PARTITION BY IndicatorName, DHBName) as DHBnumTotal,
sum(Denominator) OVER (PARTITION BY IndicatorName, DHBName) as DHBdenTotal,

sum(Numerator) OVER (PARTITION BY IndicatorName, DHBName, PHOName) as PHOnumTotal,
sum(Denominator) OVER (PARTITION BY IndicatorName, DHBName, PHOName) as PHOdenTotal,

sum(Numerator) OVER (PARTITION BY IndicatorName, DHBName, PHOName, PracticeName) as PRCnumTotal,
sum(Denominator) OVER (PARTITION BY IndicatorName, DHBName, PHOName, PracticeName) as PRCdenTotal
FROM
performanceOctober

Why push the envelope when you can just open it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources