CODEWITHSUNDEEP
sqlsql-servert-sql
John Adams
John Adams

Reputation: 4823

How to add a subquery to return count of related rows

I have a working SELECT that returns info on each company (i.e. subscriber). Certain descriptive data is returned from inner joins on "code" tables. My problem is how to add a new column to the result which is a count of the number of users belonging to each subscriber. SubscriberID in the user table is a foreign key: 

ALTER TABLE dbo.UserInfo WITH CHECK ADD CONSTRAINT FK_UserInfo_SubscriberID 
  FOREIGN KEY(SubscriberID) REFERENCES dbo.Subscriber (SubscriberID)
  ON UPDATE CASCADE
  ON DELETE CASCADE

I added the LEFT OUTER JOIN below and the GROUP BY but I cannot figure out why the parser complains that U.SubscriberID is an invalid column name). 

SELECT SubscriberID
  ,SubscriberName
  ,DaysUntilExpired
  ,SubscriptionStatus SubscriptionStatusCode
  , C.Description  SubscriptionStatus
  ,U.NumberUsers
FROM dbo.Subscriber S 
    JOIN dbo.CodeValue C ON S.SubscriptionStatus = C.Value
    JOIN dbo.CodeNamespace N ON N.ID = C.CodeNamespaceID AND N.Name = 'SubscriptionStatus'
    JOIN dbo.CodeValue V ON S.NotificationStatus = V.Value
    JOIN dbo.CodeNamespace X ON X.ID = V.CodeNamespaceID AND X.Name = 'NotificationStatus'
LEFT OUTER JOIN (SELECT Count(*) AS NumberUsers FROM dbo.UserInfo) AS U  
ON S.SubscriberID = U.SubscriberID 
GROUP BY 
    S.SubscriberID
  ,SubscriberName
  ,DaysUntilExpired
  ,S.SubscriptionStatus
  ,SubscriptionStatus

Upvotes: 1

Views: 114

Answers (2)

ARA
ARA

Reputation: 1316

Sql complains because your LEFT OUTER JOIN subquery does not include SubscriberId in column list (and in a group by clause since you have a COUNT aggregate), so it can't make the join on this column.

You should do:

LEFT OUTER JOIN (SELECT SubscriberID , Count(*) AS NumberUsers FROM dbo.UserInfo GROUP BY SubscriberID ) AS U  
ON S.SubscriberID = U.SubscriberID

Upvotes: 1

iruvar
iruvar

Reputation: 23374

You are probably looking for the following. You need to inline the subquery

SELECT SubscriberID
  ,SubscriberName
  ,DaysUntilExpired
  ,SubscriptionStatus SubscriptionStatusCode
  , C.Description  SubscriptionStatus
  ,(SELECT Count(*) AS NumberUsers FROM dbo.UserInfo where SubscriberID = S.SubscriberID) AS NumberUsers
FROM dbo.Subscriber S 
    JOIN dbo.CodeValue C ON S.SubscriptionStatus = C.Value
    JOIN dbo.CodeNamespace N ON N.ID = C.CodeNamespaceID AND N.Name = 'SubscriptionStatus'
    JOIN dbo.CodeValue V ON S.NotificationStatus = V.Value
    JOIN dbo.CodeNamespace X ON X.ID = V.CodeNamespaceID AND X.Name = 'NotificationStatus'
GROUP BY 
    S.SubscriberID
  ,SubscriberName
  ,DaysUntilExpired
  ,S.SubscriptionStatus
  ,SubscriptionStatus

Upvotes: 1

Related Questions