Invalid in Select List - Group by and aggregates

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)

Invalid in Select List - Group by and aggregates

Author

Topic

Mallen
Starting Member

27 Posts

Posted - 2010-07-07 : 18:17:00

I am trying to write a simple summary table to show the average and percents of some dates and payments. Everything works fantastic if I enter the total used in my percent calc myself but in trying to add a table to populate the total I get an error. If I try to calculate total inside the select statement it only totals within the group where I need the whole total.

I may be going about it all wrong but how can I fix this?

I get the error:
Msg 8120, Level 16, State 1, Line 18
Column '@Totals.TAccounts' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here is my code:
-- TOTALS Table
DECLARE @Totals Table
(
[TAccounts] int,
[TOriginal] money,
[TChargeoff] money,
[TBalance] money
)
INSERT INTO @Totals
SELECT COUNT(StratLoad.IssuerAcct),
SUM(StratLoad.OriginalLoanAmount),
SUM(StratLoad.ChargeOffAmt),
SUM(StratLoad.CurrentBalance)
FROM StratLoad;

-- Geographic Summary

SELECT s.State,
COUNT(s.[State]) AS Accounts,
SUM(s.CurrentBalance) AS [Balance],
(((COUNT(s.[State]))*100)/t.TAccounts) AS [%ofAccts],
((SUM(s.CurrentBalance)*100)/t.TBalance) [%ofBalance],
AVG(s.CurrentBalance) AS AvgBalance,
(CAST(FLOOR(AVG(FLOOR(CAST(s.OriginalLoanDate AS FLOAT)))) AS DATETIME)) AS [AvgOLDate],
(CAST(FLOOR(AVG(FLOOR(CAST(s.ChargeOffDate AS FLOAT)))) AS DATETIME)) AS [AvgCODate],
(CAST(FLOOR(AVG(FLOOR(CAST(s.LastPayDate AS FLOAT)))) AS DATETIME)) AS [AvgLPDate]
FROM @Totals t, StratLoad s
GROUP BY s.[State];

Thanks in advance.

tp
Starting Member

5 Posts

Posted - 2010-07-07 : 18:35:36

Hi There,

You are not using the t.TAccounts and t.TBalance in an aggregate function or a group by clause, hence the error. Try putting these two columns in the group by clause and see if that works.

Good luck!
t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources