CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008sql-server-2008-r2
Gayan
Gayan

Reputation: 1553

Get single row instead of two data rows SQL

I have sql ;

select 
Program,
CASE WHEN ClassMissed = 0 THEN COUNT(ClassMissed) ELSE 0 END as Completed,
CASE WHEN ClassMissed = 1 THEN COUNT(ClassMissed) ELSE 0 END as One_Days_Missed,
CASE WHEN ClassMissed = 2 THEN COUNT(ClassMissed) ELSE 0 END as Two_Days_Missed,
CASE WHEN ClassMissed = 3 THEN COUNT(ClassMissed) ELSE 0 ENd as Three_Days_Missed
from
#temp
group by Program,ClassMissed

Which gives me below out put:

Program	| Completed	| One_Days_Missed | Two_Days_Missed |Three_Days_Missed
________________________________________________________________________________
Prog1   |	0       |      19	  |      0	    |       0 
Prog1	|       0       |      0	  |      0	    |       2

But I'm expecting below table 

Program	| Completed	| One_Days_Missed | Two_Days_Missed |Three_Days_Missed
________________________________________________________________________________
Prog1   |	0       |      19	  |      0	    |       2

I have to do the group by Classmissed in order to run the query.

Please give me better solution for this ?

Thank you

Upvotes: 0

Views: 48

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You should use SUM(CASE WHEN..END).

CREATE TABLE #Temp(
    Program     VARCHAR(20),
    ClassMissed INT
)
INSERT INTO #Temp VALUES
('Prog1', 1), ('Prog1', 1), ('Prog1', 1),
('Prog1', 1), ('Prog1', 1), ('Prog1', 1),
('Prog1', 1), ('Prog1', 1), ('Prog1', 1),
('Prog1', 1), ('Prog1', 1), ('Prog1', 1),
('Prog1', 1), ('Prog1', 1), ('Prog1', 1),
('Prog1', 1), ('Prog1', 1), ('Prog1', 3),
('Prog1', 1), ('Prog1', 1), ('Prog1', 3);

SELECT 
    Program,
    SUM(CASE WHEN ClassMissed = 0 THEN 1 ELSE 0 END) AS Completed,
    SUM(CASE WHEN ClassMissed = 1 THEN 1 ELSE 0 END) AS One_Days_Missed,
    SUM(CASE WHEN ClassMissed = 2 THEN 1 ELSE 0 END) AS Two_Days_Missed,
    SUM(CASE WHEN ClassMissed = 3 THEN 1 ELSE 0 END) AS Three_Days_Missed
FROM #Temp
GROUP BY Program

DROP TABLE #Temp

RESULT

Program              Completed   One_Days_Missed Two_Days_Missed Three_Days_Missed
-------------------- ----------- --------------- --------------- -----------------
Prog1                0           19              0               2

Upvotes: 1

Related Questions