CODEWITHSUNDEEP
sqlt-sqlselectcountsubquery
PLan
PLan

Reputation: 133

Transact-SQL Count using a subselect statement filtering on a variable from top select

I'm trying to include account of records associated with a particular user in the same row as other information about the user. Something like:

select
    au.UserName as UsersName,
    Count(
        select sg.Id from sg
        where sg.Username = UsersName
     )

...

Is something like this possible?

Upvotes: 2

Views: 98

Answers (3)

paparazzo
paparazzo

Reputation: 45096

select au.UserName as UsersName, count(sg.Id)
  from au 
  join sg 
    on sg.Username = au.UserName
 group by au.UserName

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

Use column name instead of alias name

select
    au.UserName as UsersName,
    (
        select count(sg.Id) from sg
        where sg.Username = au.UserName
     ) as Count
...

DEMO http://sqlfiddle.com/#!3/8b62d/10

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Put the count() inside the select:

select au.UserName as UsersName,
       (select count(sg.Id)
        from sg
        where sg.Username = au.UserName
       )

The correlation also cannot use the column alias. It needs to use the part before the as. The alias is outside the scope of the subquery.

Upvotes: 1

Related Questions