How to get count 0 using group by if record not exist?

Question

I have sql server query and i want to get count=0 if record not exist. I want to show all from category table , show all categories with count 0 if not exist. But its return empty if record not exist in where IN clause.

  SELECT TC.DESCRIPTION,count(TE.CategoryID) AS COUNT 
  FROM tblEvent TE
  right JOIN tblCategory TC on TE.CategoryID=TC.NO
  WHERE TE.AssetID IN (
  SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3)
  GROUP BY  TE.CategoryID,TC.DESCRIPTION 

I want to show all categories from category table also with count 0. I am sorry, i cannot explain more, hope understand.I also try all the joins left,right,left out,right outer but its show empty but when record exist in WHERE IN caluse then its show data otherwise not showing count 0 according to category table.

Answer 1

Hameed,

I believe the challenge you have is you used the IN() command as part of the WHERE when the filter applied to the "conditional table".

(If we re-write the query using a LEFT OUTER JOIN and some slight reformatting just to help me see it better.)

SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
WHERE TE.AssetID IN (
                    SELECT ASSET_NO 
                    FROM tblAsset 
                    WHERE EQUIPMENT_ID=3
                    )
GROUP BY  TE.CategoryID,TC.DESCRIPTION 

What you are first asking the query engine to do is:

1. Get All tblCategory records
2. Find all related tblEvent records where tblEvent.CategoryID = tblCategory.NO

This ends up creating something that could look like this (I may have data types wrong for what is actually in your table but I just wanted to get a concept communicated):

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
| E                       | NULL                | NULL             |
| F                       | NULL                | NULL             |
| G                       | 6                   | 3                |
| H                       | 6                   | 3                |
| I                       | 6                   | 4                |
| J                       | 6                   | 5                |
+-------------------------+---------------------+------------------+

When the WHERE condition is applied, this entire result set is then filtered to only show the records which have the appropriate tblEvent.AssetID. For the sake of the example lets say that SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 only returns the value of '1' and '2'. What is then left over (before applying the GROUP BY and then COUNT pieces) is:

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
+-------------------------+---------------------+------------------+

If you slightly modify the query and put this WHERE condition as part of the JOIN your result set will be different but you won't loose the tblCategory records. This happens because all the tblCategory records stay in tact (because we are LEFT OUTER JOIN'ing (which is the same thing as just LEFT JOIN, I just like using the OUTER word as well)) but, not only does tblEvent.CategoryID need to equal tblEvent.NO. But tblEvent.AssetID must also match the result of SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 in order to come back to the main SELECT.

SELECT TC.DESCRIPTION, count(TE.CategoryID) AS COUNT 
FROM tblCategory TC 
    LEFT OUTER JOIN tblEvent TE
        on TE.CategoryID=TC.NO
        AND TE.AssetID IN (
                            SELECT ASSET_NO 
                            FROM tblAsset 
                            WHERE EQUIPMENT_ID=3
                            )
GROUP BY  TE.CategoryID,TC.DESCRIPTION 

This should return the below table right before running your GROUP BY and COUNT pieces (again assuming SELECT ASSET_NO FROM tblAsset WHERE EQUIPMENT_ID=3 only returns the value of '1' and '2'):

+-------------------------+---------------------+------------------+
| tblCategory.Description | tblEvent.CategoryID | tblEvent.AssetID |
+-------------------------+---------------------+------------------+
| Test                    | 1                   | 1                |
| Test 1                  | 1                   | 1                |
| Other                   | 2                   | 1                |
| Other 1                 | 3                   | 1                |
| A                       | 3                   | 2                |
| B                       | 3                   | 2                |
| C                       | 4                   | 2                |
| D                       | 4                   | 2                |
| E                       | NULL                | NULL             |
| F                       | NULL                | NULL             |
| G                       | NULL                | NULL             |
| H                       | NULL                | NULL             |
| I                       | NULL                | NULL             |
| J                       | NULL                | NULL             |
+-------------------------+---------------------+------------------+

This result set should return the values you are looking for, or at least get you a lot closer.

Does this give you the guidance you are looking for?

Answer 2

THE CODE THAT WILL GET THE DESIRED OUTPUT IS :

SELECT
TC.DESCRIPTION,
SUM(CASE WHEN TE.CategoryID IS NULL THEN 0 ELSE 1 END) COUNT
FROM tblCATEGORY TC LEFT JOIN
(tblEVENT TE INNER JOIN tblAsset TA ON TE.AssetID = TA.ASSET_NO AND TA.EQUIPMENT_ID = 3) ON TC.NO = TE.CategoryID
GROUP BY TC.DESCRIPTION;