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

Question

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?

score 1 · Answer 1 · answered Dec 31 '14 at 19:28

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.

HaveNoDisplayName · Answer 2 · 2014-12-31T19:33:47.817

0

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
...

edited Dec 31 '14 at 19:33

answered Dec 31 '14 at 19:22

HaveNoDisplayName

Will the value selected for au.Username be the same as the one chosen outside of the subselect statement? – PLan Dec 31 '14 at 19:24
This works, thanks. Putting count() inside the select is what did it. – PLan Dec 31 '14 at 19:44

score 0 · Answer 3 · answered Jan 01 '15 at 21:02

0

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

answered Jan 01 '15 at 21:02

paparazzo

3 Answers3