Friday, July 19, 2013

Make a query Count() return 0 instead of empty in group by

I want to display Class name and student count who Registered within two days in my table. So in the normal group by if you don’t have any value for that particular WHERE condition means it will display empty message. But I need classname and student count zero instead of empty. For that the below query will help you to display classname and student count (replace the empty to 0).



SELECT A.ClassName, isnull(B. StudentCount, 0) as StudentCount
FROM (
    SELECT DISTINCT ClassName
    FROM StudentMaster
) A
LEFT JOIN (
    SELECT ClassName, COUNT(*) AS StudentCount
    FROM StudentMaster
     where (CreatedDate between Getdate()-1 and Getdate())
    GROUP BY ClassName
) B
ON A. ClassName = B. ClassName


The above SQL Query will help you to get the
SQL count(*) and group by including zero
Make a query Count() return 0 instead of empty
Return 0 when result is empty
Make a query Count() return 0 instead of empty in group by

No comments: