CODEWITHSUNDEEP
sqlt-sqlsubquerycaseaggregate-functions
Joe-IV
Joe-IV

Reputation: 75

Aggregate Function on an Expression Containing A Subquery

With the following t-sql query:

select u.userid
into #temp
from user u
where u.type = 1;

select top 50
    contentid,
    count(*) as all_views,
    sum(case when hc.userid in (select userid from #temp) then 1 else 0 end) as first_count,
    sum(case when hc.userid in (40615, 40616) then 1 else 0 end) as another_count
from hitcounts hc
inner join user u on u.userid = hc.userid
group by hc.contentid
order by count(*) desc;

I get an error message

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

However, if just include the column 'another_count' (with the hard-coded list of identifiers), everything works as I expected. Is there a way I should go about only getting the count for userids contained within a subquery? I plan to have multiple columns, each counting up a set/subquery of different userids.

Performance is not a concern at this point and I appreciate any guidance.

Upvotes: 0

Views: 652

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You don't need a temporary table for this purpose. Just use a conditional aggregation:

select top 50 contentid,
       count(*) as all_views,
       sum(case when u.type = 1 then 1 else 0 end) as first_count,
       sum(case when hc.userid in (40615, 40616) then 1 else 0 end) as another_count
from hitcounts hc join
     user u
     on u.userid = hc.userid
group by hc.contentid
order by count(*) desc;

Upvotes: 1

Related Questions