How do I aggregate this table?

• Nuts

  SSCrazy

  Points: 2547

  More actions

  January 21, 2007 at 3:49 pm

  #194005

  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]

• SwePeso

  SSC-Dedicated

  Points: 39725

  More actions

  January 22, 2007 at 1:12 am

  #684614

  Answered here

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77856

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  January 23, 2007 at 6:29 pm

  #685121

  This also works....

  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

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg