Get some set of values in SQL Server

I have a query, in this query I need to get a number of classes (class count) in each district. In here it comes only those who have classes. So I need to show the district’s it doesn’t have a classes as well.

My District table consist 25 values. and my class coverage table has 5 values to 3 districts. I need to show all the districts.

Code

SELECT 
    CC.DistrictId, D.DistrictName,
    COUNT(D.DistrictId) AS DistrictCount
FROM
    TBL_T_ClassCoverage CC
LEFT JOIN 
    [dbo].[TBL_M_District] D ON CC.DistrictId = D.DistrictId
WHERE 
    CC.IsActive = 1 
GROUP BY
    CC.DistrictId, D.DistrictName
ORDER BY
    DistrictId ASC

Answer

You need to join your tables the other way, from districts to classes, to ensure that all district values are in the output:

  SELECT D.DistrictId, D.DistrictName, COUNT(CC.DistrictId) AS DistrictCount
  FROM [dbo].[TBL_M_District] D
  LEFT JOIN TBL_T_ClassCoverage CC
  ON CC.DistrictId = D.DistrictId AND CC.IsActive = 1 
  GROUP BY D.DistrictId, D.DistrictName
  ORDER BY D.DistrictId ASC