SQL Query - If condition
-
Hi Friends, I am using SQL Server 2005. I am facing the following problem. I need to know how to apply if condition in query. I am having one table called Test1 with two fields c_name, average. CC 7 CC 6 CC 8 IGC 2 IGC 4 IGC 6 HC 4 HC 5 NM 9 NM 6 NC 2 NC 5 NC 9 JC 4 JC 3 JC 1 The above are the values in that table. Now I am using the following query to display the c_name, count(college) with average >5 group by c_name select c_name,count(c_name) as Total from test1 where average>5 group by c_name Result : CC 3 IGC 1 NC 1 NM 2 Based on my query I am getting the above result. In the above result the colleges HC and JC are not coming because it has average only less than 5. But for my case, I want to display the college name with the total as 0 if that college doesnt have average >5. I need to modify the above query to get the result like below. CC 3 IGC 1 NC 1 NM 2 HC 0 JC 0 Please help for this. Thanks in Advance, Regards,
-
Hi Friends, I am using SQL Server 2005. I am facing the following problem. I need to know how to apply if condition in query. I am having one table called Test1 with two fields c_name, average. CC 7 CC 6 CC 8 IGC 2 IGC 4 IGC 6 HC 4 HC 5 NM 9 NM 6 NC 2 NC 5 NC 9 JC 4 JC 3 JC 1 The above are the values in that table. Now I am using the following query to display the c_name, count(college) with average >5 group by c_name select c_name,count(c_name) as Total from test1 where average>5 group by c_name Result : CC 3 IGC 1 NC 1 NM 2 Based on my query I am getting the above result. In the above result the colleges HC and JC are not coming because it has average only less than 5. But for my case, I want to display the college name with the total as 0 if that college doesnt have average >5. I need to modify the above query to get the result like below. CC 3 IGC 1 NC 1 NM 2 HC 0 JC 0 Please help for this. Thanks in Advance, Regards,
-
Try this:
select c_name, sum(case when average > 5 then 1 else 0 end) as Total
from Test1
group by c_nameBob Ashfield Consultants Ltd