Aggregate Query Help Required

• dec_obrien

  Default port

  Points: 1486

  More actions

  October 20, 2014 at 3:43 am

  #294229

  Hi,

  I have the following table:

  CREATE TABLE [dbo].[Table_1](

  [ID] [int] IDENTITY(1,1) NOT NULL,

  [FK_ID] [int] NOT NULL,

  [Period] [int] NOT NULL,

  [Score] [float] NOT NULL

  ) ON [PRIMARY]

  GO

  SET IDENTITY_INSERT [dbo].[Table_1] ON

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (1, 1, 201410, 2.3)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (3, 2, 201410, 2.2)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (4, 3, 201410, 2.4)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (5, 4, 201410, 108)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (6, 5, 201410, 107)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (7, 6, 201410, 103)

  INSERT [dbo].[Table_1] ([ID], [FK_ID], [Period], [Score]) VALUES (8, 7, 201410, 106)

  SET IDENTITY_INSERT [dbo].[Table_1] OFF

  I need to group together one set FK_IDs and divide them by another set of FK_IDs for a report

  e.g. 1, 2 and 3 divide by 4, 5, 6 and 7

  so it would result in (2.3 + 2.2 + 2.4)/ (108 + 107 + 103 + 106)

  is there any way to achieve this in one step? The groupings of the FK_IDs will come from a table.

  THanks for any guidance...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 20, 2014 at 3:50 am

  #1753441

  Please can we see the table that contains the grouping information?

  I think the best way will be to add up and and number the groups in a CTE, then do the division in the main part of the query.

  John

• serg-52

  SSCrazy Eights

  Points: 9913

  More actions

  October 20, 2014 at 4:19 am

  #1753445

  Kind of

  declare @groups table (

  id int,

  gname varchar(10)

  );

  insert @groups values

  (1,'Dividend')

  ,(2,'Dividend')

  ,(3,'Dividend')

  ,(4,'Divisor')

  ,(5,'Divisor')

  ,(6,'Divisor')

  ,(7,'Divisor')

  ;

  select [Period], sum(case gname when 'Dividend' then [Score] end) / sum(case gname when 'Divisor' then [Score] end)

  from [Table_1] as t

  join @groups as g on t.FK_ID = g.id

  group by [Period]

  ;

• dec_obrien

  Default port

  Points: 1486

  More actions

  October 20, 2014 at 4:54 am

  #1753454

  That looks like exactly what I want to do. I'll give it a go and come back to you.

  Thanks for the help.

• dec_obrien

  Default port

  Points: 1486

  More actions

  October 20, 2014 at 7:54 am

  #1753506

  Hi,

  Got it to work. Here is the other table:

  CREATE TABLE [dbo].[tbl_Calculation](

  [CalcID] [int] IDENTITY(1,1) NOT NULL,

  [CalcKPI_ID] [int] NOT NULL,

  [FK_KPI_ID] [int] NOT NULL,

  [KPI_Operation] [varchar](50) NOT NULL

  )

  Then:

  WITH Separated_Info(Period, TopLine, BottomLine) AS

  (SELECT Period,

  CASE KPI_Operation WHEN 'TOP' THEN Table_1.Score END AS TopLine

  ,CASE KPI_Operation WHEN 'BOTTOM' THEN Table_1.Score END AS BottomLine

  FROM Table_1 INNER JOIN

  tbl_Calculation ON Table_1.FK_ID = tbl_Calculation.FK_KPI_ID

  )

  SELECT SUM(TopLine)/ Sum(BottomLine)

  FROM Separated_Info

  GROUP BY Period

  Thanks for the help guys.