CODEWITHSUNDEEP
sqlsql-servercount
User999999
User999999

Reputation: 2520

SQL subQuery with COUNT() in WHERE/HAVING Statement

Consider following subquery. This query retrieves all the personalIds of employees who have more then 1 'IDKleerkastPersoon' in their possession. For this query i'm useing a COUNT() statement in my HAVING Clause. This subuqery returns all the the PersonalIds for there employees.

(SELECT     DISTINCT Persoon_1.Stamnr
 FROM          dbo.KleerkastPerPersoon AS KleerkastPerPersoon_1 INNER JOIN
                                               dbo.Persoon AS Persoon_1 ON KleerkastPerPersoon_1.ID_Persoon = Persoon_1.ID_Persoon
                        GROUP BY Persoon_1.Stamnr, Persoon_1.ID_Afdeling, KleerkastPerPersoon.IDKleerkastPersoon, Persoon.Naam
                        HAVING      (Persoon_1.ID_Afdeling = 2) AND (COUNT(KleerkastPerPersoon.IDKleerkastPersoon) >= 2)
                        ORDER BY Persoon_1.Stamnr DESC)

Now user requested some more information then solely the PersonalId of the employees. So I wrote a query above it (see below) which retrieves more global info about the employee. As expected, sql server rejects this method.

SELECT     dbo.Persoon.Stamnr, dbo.Persoon.Naam, dbo.Persoon.Voornaam, dbo.Refter.RefterOmschrijving, dbo.Kleedkamer.KleedkamerOmschrijving, 
                  dbo.Kleerkast.KleerkastOmschrijving
FROM         dbo.KleerkastPerPersoon INNER JOIN
                  dbo.Persoon ON dbo.KleerkastPerPersoon.ID_Persoon = dbo.Persoon.ID_Persoon INNER JOIN
                  dbo.Kleerkast INNER JOIN
                  dbo.Kleedkamer ON dbo.Kleerkast.ID_Kleedkamer = dbo.Kleedkamer.ID_Kleedkamer INNER JOIN
                  dbo.Refter ON dbo.Kleedkamer.ID_Refter = dbo.Refter.ID_Refter ON dbo.KleerkastPerPersoon.ID_Kleerkast = dbo.Kleerkast.ID_Kleerkast
WHERE     (dbo.Persoon.Stamnr IN <<<Result of my first subquery>>>
                     )

The error message being thrown is:

An aggregate may not appear in the where clause unless it is in a subquery contained in a having clause or a select list, and the column being aggregated is an outer reference

So i moved my subquery to the having clause:

<<<Query 2>>>
HAVING      (dbo.Persoon.Stamnr IN
                      (SELECT     TOP (100) PERCENT Persoon_1.Stamnr
                        FROM          dbo.KleerkastPerPersoon AS KleerkastPerPersoon_1     INNER JOIN
                                               dbo.Persoon AS Persoon_1 ON KleerkastPerPersoon_1.ID_Persoon = Persoon_1.ID_Persoon
                        GROUP BY Persoon_1.Stamnr, Persoon_1.ID_Afdeling
                        HAVING      (Persoon_1.ID_Afdeling = 2) AND (COUNT(KleerkastPerPersoon.IDKleerkastPersoon) >= 2)
                        ORDER BY Persoon_1.Stamnr DESC))

But now the query doesn't return any results. While I should be seeing 98 different records. Does any of you have a solution to my problem or my workaround? How can i remove the need for a subquery for instance.

Upvotes: 0

Views: 465

Answers (3)

Achim Schmitz
Achim Schmitz

Reputation: 538

I think the aggregate in the error message refers to the GROUP BY clause and the COUNT(KleerkastPerPersoon.IDKleerkastPersoon) in your first query.

When you use this select as a sub-query, you are apparently referencing something which is not the result of a HAVING clause or a SELECT statement. You should include any columns or aggregates you need in the result set of your sub-query. You should also leave the ORDER BY part out in the sub-query (assuming you haven't done so).

I can't see exactly what the offending reference is, but I agree with mhasan, that you should, at least initially, break your query down by using some temporary tables and when you get it working put it all back together into a single query.

Upvotes: 0

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Well If I get what you are after something like

SELECT dbo.Persoon.Stamnr, dbo.Persoon.Naam, dbo.Persoon.Voornaam, dbo.Refter.RefterOmschrijving, dbo.Kleedkamer.KleedkamerOmschrijving, dbo.Kleerkast.KleerkastOmschrijving
FROM dbo.KleerkastPerPersoon
inner join (your original query) SomeSuitableAlias
On SomeSuitableAlias.Stamnr = dbo.KleerkastPerPersoon.Stamnr

should do the job.

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Store the result of first subquery in a table variable and then use it in your final query. Also no need of Order By clause in the subquery unless you are using TOP.

Upvotes: 2

Related Questions