question

Karthik Venkatraman avatar image
Karthik Venkatraman asked

How to count based on multiple columns

Hi People, I am writing a sql query that will display the count of records for a particular name. The main thing in this i need to find the count for three columns for a particular name. For example for a person name ABC there can be three rows with some values in each column. SELECT EMPLOYEE_NAME,[PASTDUE>60<90],[PASTDUE>90<120],[PASTDUE>120] FROM dbo.training_report query returns Person Name Day 1 day 2 day 3 ABC NULL 1 NULL ABC 1 NULL NULL ABC NULL NULL 1 ABC NULL NULL 1 ABC NULL 1 NULL BAC NULL NULL 1 DAE NULL NULL 1 If we use the regular count statement, it displays three rows with count of each column in each row. select distinct EMPLOYEE_NAME, count(Day1) as [DAYSPASTDUE > 60 < 90], count(Day2) as [DAYSPASTDUE > 60 < 90], count(Day3) from db_ITG.dbo.training_report where Day1 <> 0 or Day2 <> 0 or Day3 <> 0 group by EMployee_Name,Day1, Day2,Day3 returns the following results Person Name Day 1 day 2 day 3 --------------------------------------------------------------------------------------- ABC NULL 2 NULL ABC 1 NULL NULL ABC NULL NULL 2 BAC NULL NULL 1 DAE NULL NULL 1 i need the result to be a single row with count of each column. can anyone help me with this. Person Name Day 1 day 2 day 3 --------------------------------------------------------------------------------------- ABC 1 2 2 BAC NULL NULL 1 DAE NULL NULL 1 Please help me in this as I need to complete my record work for my college. It would be highly helpful if some one post me the code for the result i need. Thanks in advance.
tsqlhomeworkaggregatescount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
It is your Group By statement that is messing you up. Also using count get the number of rows. You want to sum the column instead. SELECT DISTINCT EMPLOYEE_NAME, SUM(DAY1) AS [DAYSPATDUE > 60 < 90], SUM(DAY2) AS [DAYSPASTDUE >60 < 90], SUM(DAY3) FROM db_ITG.dbo.training_report GROUP BY EMPLOYEE_NAME
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
SELECT NAME, SUM(CASE WHEN DaysPastDue between 60 AND 90 THEN 1 ELSE 0 END) AS [Day 1], SUM(CASE WHEN DaysPastDue between 90 AND 120 THEN 1 ELSE 0 END) AS [Day 2], SUM(CASE WHEN DaysPastDue > 120 THEN 1 ELSE 0 END) AS [Day 3] FROM dbo.Training_Report GROUP BY NAME
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Phil, Thanks a lot for your help. I have just replaced sum instead of count. select distinct EMPLOYEE_NAME, SUM([PASTDUE>60<90]) as [DAYSPASTDUE > 60 < 90], Sum([PASTDUE>90<120]) as [DAYSPASTDUE > 60 < 90], Sum([PASTDUE>120]) from training_report where [PASTDUE>60<90] <> 0 or [PASTDUE>90<120] <> 0 or [PASTDUE>120] <> 0 group by Employee_Name
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.