Using SELECT as column

Question

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

Gordon Linoff · Accepted Answer · 2016-11-16 02:39:02Z

2

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.

answered Nov 16, 2016 at 2:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SELECT as column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related