Not in an aggregate function - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Not in an aggregate function

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-21 : 07:31:00

My query as follows gives an error about Centre not being in an aggregate function so any help would be appreciated.

SELECT Q1a AS Question, CAST(COUNT(Q1a) AS NUMERIC) /
(SELECT COUNT(*)
FROM dbo.tblQuestionnaireNP) AS Total
FROM dbo.tblQuestionnaireNP
GROUP BY Q1a
HAVING (Centre = 107)

www.creativenrg.co.uk

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-21 : 07:35:07

Centre needs to be a part of the SELECT to be able to use it in a HAVING clause:

SELECT Q1a AS Question, CAST(COUNT(Q1a) AS NUMERIC) /
(SELECT COUNT(*)
FROM dbo.tblQuestionnaireNP) AS Total, Centre
FROM dbo.tblQuestionnaireNP
GROUP BY Q1a, Centre
HAVING (Centre = 107)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-21 : 08:05:10

[code]SELECT Q1a AS Question, CAST(COUNT(Q1a) AS NUMERIC) /
(SELECT COUNT(*) FROM dbo.tblQuestionnaireNP) AS Total
FROM dbo.tblQuestionnaireNP
WHERE Centre = 107
GROUP BY Q1a[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-21 : 08:06:01

I think you dont need in having HAVING. Just use Where clause
SELECT Q1a AS Question, CAST(COUNT(Q1a) AS NUMERIC) /
(SELECT COUNT(*)
FROM dbo.tblQuestionnaireNP) AS Total
FROM dbo.tblQuestionnaireNP
Where (Centre = 107)
GROUP BY Q1a

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-21 : 08:06:49

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-21 : 08:25:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources