CODEWITHSUNDEEP
sqlsql-server-2008
e-MEE
e-MEE

Reputation: 508

SQL Server : cannot perform an aggregate function

Can someone explain me why this works:

select val1, val2, 
   count(case when table2.someID in (1, 2, 3, 48967, 123456) then table2.someId end) as val3
from table1
join table2 on table1.someId = table2.someId
where blabla
group by val1, val2
order by val1

but this query here:

select val1, val2, 
   count(case when table2.someID in (Select someId from table567) then table2.someId end) as val3
from table1
join table2 on table1.someId = table2.someId
where blabla
group by val1, val2
order by val1

gives error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

(someId is PK in table2)

Upvotes: 0

Views: 659

Answers (2)

GarethD
GarethD

Reputation: 69759

You can get around this by using a left join rather than a subquery.

select  val1, 
        val2, 
        count(table567.someID) as val3
from    table1
        join table2 on table1.someId = table2.someId
        left join table567 on table2.someID = table567.someID
where blabla
group by val1, val2
order by val1

Upvotes: 3

psur
psur

Reputation: 4519

Well, if it "Cannot perform an aggregate function on an expression containing (...) subquery" then reason is simple: in second query you have subquery (Select someId from table567) in aggregate function count and in first you don't.

Upvotes: 4

Related Questions