Use A Sub-Query With A Case Statement

Question

This may be impossible to achieve, and if so how would I re-write my query to still achieve the same results? I keep getting this error:

Msg 130, Level 15, State 1, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

And this is my syntax

select
count(case when username in (select username from database0254.dbo.userinformation) then 1 else 0 end) As [Active User]
,count(case when name in (select fullname from database0254.dbo.names) then 1 else 0 end) As [Valid Name]
From users

Answer 1

While there are other ways and other improvements that could be made, you should be able to simple move part of your logic into a common table expressions (CTE):

WITH cte AS (
    select case when username in (select username from database0254.dbo.userinformation) then 1 else 0 end As [Active User]
          ,case when name in (select fullname from database0254.dbo.names) then 1 else 0 end As [Valid Name]
    From users
)
SELECT SUM([Active User]) [Active User]
      ,SUM([Valid Name]) [Valid Name]
  FROM cte

Note: we are changing your COUNT to SUM.

Answer 2

Try this instead:

select    sum(case when ui.username is null then 0 else 1) As [Active User]
          ,sum(case when n.fullname is null then 0 else 1) As [Valid Name]
from      users u
left join database0254.dbo.userinformation ui on u.username = ui.username
left join database0254.dbo.names n on u.name = u.fullname