CODEWITHSUNDEEP
sql-serversubquery
user6689924
user6689924

Reputation: 15

Using SELECT as column

I'm using this query

SELECT
     fullname,
     (SELECT 
          count([ID]) FROM [SDT-DB].[dbo].[tbl_InfoBoard_UserLogs] 
          WHERE fullname = fullname AND user_did ='login') AS [Login],
     (SELECT 
          count([ID]) FROM [SDT-DB].[dbo].[tbl_InfoBoard_UserLogs] 
          WHERE fullname = fullname AND user_did ='message') AS [View Messageboard],
     (SELECT 
          count([ID]) FROM [SDT-DB].[dbo].[tbl_InfoBoard_UserLogs] 
          WHERE fullname = fullname AND user_did ='notification') AS [View Notification],
     count([ID]) AS Count
FROM [SDT-DB].[dbo].[tbl_InfoBoard_UserLogs]
GROUP BY fullname

it seems its returning all userlogs...

Upvotes: 0

Views: 5196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use conditional aggregation:

SELECT fullname,
       SUM(CASE WHEN user_did = 'login' THEN 1 ELSE 0 END) AS [Login],
       SUM(CASE WHEN user_did = 'message' AS [View Messageboard],
       SUM(CASE WHEN user_did = 'notification' THEN 1 ELSE 0 END) AS [View Notification],
       count([ID]) AS Count
FROM [SDT-DB].[dbo].[tbl_InfoBoard_UserLogs]
GROUP BY fullname;

Your logic doesn't work because you have no correlation clause. You seem to intend to have one with fullname = fullname, but that just evaluates to true whenever fullname is not NULL. But, the above is simpler.

Upvotes: 2

Related Questions