Summing A Column that has been Aggregated

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)

Summing A Column that has been Aggregated

Author

Topic

ackrite55
Starting Member

3 Posts

Posted - 2009-12-04 : 10:31:11

How can I rewrite the following to work around summing an aggregated column?

CREATE TABLE ##mytblvst

(
Date DATETIME,
Tokens Money,
P_Hold Numeric,
Gains Money,
Theo_Gains Numeric)
insert into ##mytblvst
SELECT 'Oct 15 2007 12:00AM',500.00,10.0300,55.09,5.07 UNION ALL
SELECT 'Oct 15 2007 12:00AM',123.78, 10.0300,10.89, 1.08 UNION ALL
SELECT 'Oct 13 2007 12:00AM', 78.45, 8.5793,10.89,3.90 UNION ALL
SELECT 'Oct 13 2007 12:00AM',207.56, 5.2471,55.08,10.78 UNION ALL
SELECT 'Oct 12 2007 12:00AM',145.89, 5.1177,5.07,1.09 UNION ALL
SELECT 'Oct 13 2007 12:00AM', 178.65, 9.5510,11.06,2.08 UNION ALL
SELECT 'Oct 13 2007 12:00AM',300.87, 12.5128,2.07,1.06 UNION ALL
SELECT 'Oct 15 2007 12:00AM',89.90, 3.5758,23.09,5.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, p_hold

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
SELECT
convert(varchar, date, 101)as 'Date'
,SUM ([Tokens])Total_Tokens

,SUM([Gains])Total_Gains
, sum((P_hold/100)* SUM(TOKENS)) THEO_GAINS
FROM ##mytblvst
group by date, P_HOLD

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 11:10:56

If you are grouping by percentage then you dont need both sums...

(perecentage/100)* SUM ([investment]) will work fine.

Although I'm not so sure yoru statement 'Group by Date' will work.

You can not use getdate() in a grouping, put a where clause on a date field = Getdate() if you would like. Or remove the date grouping altogether.

ackrite55
Starting Member

3 Posts

Posted - 2009-12-04 : 14:18:47

I rewritten it to provide full scope.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-04 : 14:33:58

Since you already have (P_hold/100)* SUM(TOKENS) written how you want it, you turn that data into a derived table.

SELECT
convert(varchar, date, 101)as 'Date'
,SUM ([Tokens])Total_Tokens

,SUM([Gains])Total_Gains
, Sum(b.THEO_GAINS) as SUM_THEO_GAINS
FROM ##mytblvst a
left outer join
(convert(varchar, date, 101)as 'Date'
,SUM ([Tokens])Total_Tokens
,SUM([Gains])Total_Gains
,(P_hold/100)* SUM(TOKENS) AS THEO_GAINS
FROM ##mytblvst
group by date, P_HOLD) b on a.Date = b.Date

group by date, P_HOLD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources