CODEWITHSUNDEEP
sqlt-sql
S Nash
S Nash

Reputation: 2499

How to join result of a Group by Query to another table

I have the following Group By SQL:

SELECT
 s.Login_Name, COUNT(s.s1CIDNumber)
  FROM [dbSuppHousing].[dbo].[tblSurvey] s
  group by s.Login_Name

I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name)

I tried the following:

Select  u.User_Id from tblUsers u,a.Login_Name
inner join
(SELECT
 s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc
  FROM [dbSuppHousing].[dbo].[tblSurvey] s
  group by s.Login_Name) a

 on 
 u.Login_Name=a.Login_Name

I get errors. The problem is columns of a are not visible outside.

For example a.abc

Upvotes: 0

Views: 594

Answers (4)

John
John

Reputation: 4006

Some thing like this should do it.

select
    s.Login_Name, 
    u.col_a,
    u.col_b,
    u.col_c,
    count(s.s1CIDNumber)
from 
    [dbSuppHousing].[dbo].[tblSurvey] s
    join tblUsers t on s.user_id = t.user_id
group by 
    s.Login_Name
    u.col_a,
    u.col_b,
    u.col_c

Upvotes: 0

Dasman
Dasman

Reputation: 317

The from should reference a table not a column:

Select  u.User_Id, a.Login_Name from tblUsers u
inner join

Other than that it should work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Your from clause is in the wrong place:

Select  u.User_Id, a.*
from tblUsers u inner join
     (SELECT s.Login_Name, COUNT(s.s1CIDNumber) as abc
      FROM [dbSuppHousing].[dbo].[tblSurvey] s
      GROUP BY s.Login_Name
     ) a
     ON u.Login_Name = a.Login_Name;

Upvotes: 0

The Reason
The Reason

Reputation: 7973

You have mistake here from tblUsers u,a.Login_Name try to move this piece of code a.Login_Name to select 

Select  u.User_Id, a.Login_Name from tblUsers u
inner join
(SELECT
 s.Login_Name Login_Name, COUNT(s.s1CIDNumber)as abc
  FROM [dbSuppHousing].[dbo].[tblSurvey] s
  group by s.Login_Name) a

 on 
 u.Login_Name=a.Login_Name

Upvotes: 1

Related Questions