Summing An Aggregated Column

• ackrite55

  Mr or Mrs. 500

  Points: 562

  More actions

  December 4, 2009 at 11:37 am

  #137470

  How do I sum the derived column, Theo_Gains for the day if I already used Sum() to calculate the The_Gain for each P_hold? So the result is grouped by the date as in table 2 with Sum(Theo_Gains )becoming the 3rd aggregated column?

  CREATE TABLE ##mytblvst

  (

  Date DATETIME,

  Tokens Money,

  P_Hold Numeric,

  Gains Money)

  insert into ##mytblvst

  SELECT 'Oct 15 2007 12:00AM',500.00,10.0300,55.09 UNION ALL

  SELECT 'Oct 15 2007 12:00AM',123.78, 10.0300,10.89 UNION ALL

  SELECT 'Oct 13 2007 12:00AM', 78.45, 8.5793,10.89 UNION ALL

  SELECT 'Oct 13 2007 12:00AM',207.56, 5.2471,55.08 UNION ALL

  SELECT 'Oct 12 2007 12:00AM',145.89, 5.1177,5.07 UNION ALL

  SELECT 'Oct 13 2007 12:00AM', 178.65, 9.5510,11.06 UNION ALL

  SELECT 'Oct 13 2007 12:00AM',300.87, 12.5128,2.07 UNION ALL

  SELECT 'Oct 15 2007 12:00AM',89.90, 3.5758,23.09

  SELECT * FROM ##mytblvst

  SELECT

  convert(varchar, date, 101)as 'Date'

  ,SUM ([Tokens])Total_Tokens

  ,SUM([Gains])Total_Gains

  /*,(P_hold/100)* SUM(TOKENS) THEO_GAINS*/

  FROM ##mytblvst

  group by date

  select date, Total_Tokens, Total_Gains, Sum (Theo_Gains) from

  (SELECT

  convert(varchar, date, 101)as 'Date'

  ,SUM ([Tokens])Total_Tokens

  ,SUM([Gains])Total_Gains

  , (P_hold/100)* SUM(TOKENS) THEO_GAINS

  FROM ##mytblvst

  group by date, p_hold, tokens)theo

  group by date , total_tokens, total_Gains

• Nabha

  SSCrazy Eights

  Points: 8534

  More actions

  December 5, 2009 at 1:20 am

  #1088130

  I am sure I am miles away from what the solution is (as your requirement was not clear) but an attempt to provide solution.

  Is this what you want?

  Select *, (p_hold/100 * Total_tokens) as theo_gains from (SELECT

  convert(varchar, date, 101)as 'Date'

  ,SUM ([Tokens])Total_Tokens

  ,SUM([Gains])Total_Gains,

  p_hold

  FROM #mytblvst

  group by date, P_HOLD ) t

  ---------------------------------------------------------------------------------

• ackrite55

  Mr or Mrs. 500

  Points: 562

  More actions

  December 7, 2009 at 9:23 am

  #1088656

  (p_hold/100 * Total_tokens) should be summed as a derived column.

  Sum((p_hold/100 * Total_tokens)) yields an error.

• Timothy J Hartford

  SSCarpal Tunnel

  Points: 4214

  More actions

  December 8, 2009 at 9:47 am

  #1089342

  Not 100% sure what you are after. What is the aggregation level of each item?

  here are some quick thoughts, maybe I got lucky and one of these is what you are looking for:

  SELECT A.*, A.P_Hold / 100 * Total_Tokens

  FROM ##mytblvst A

  CROSS JOIN (SELECT SUM(Tokens) AS Total_Tokens FROM ##mytblvst) B

  SELECT A.Date, SUM(A.Gains), SUM(A.P_Hold) / 100 * MAX(Total_Tokens)

  FROM ##mytblvst A

  CROSS JOIN (SELECT SUM(Tokens) AS Total_Tokens FROM ##mytblvst) B

  GROUP BY A.Date

  SELECT A.Date, SUM(A.P_Hold) / 100 * SUM(Tokens)

  FROM ##mytblvst A

  GROUP BY Date