SQL - Returning all rows even if count is zero for item

Question

I am performing a count based on a date range. Currently the query does return the correct result but I require additional information. In it's current form, the query shows the item with the correct count. However I need all items to be shown, even if their count is zero for the date range specified. Here is the SQL code: ``` INSERT INTO @CreationCount (BaselineID, Name) SELECT distinct [BaselineID],[Name] FROM [Baseline_INFO] DECLARE @ReqType TABLE (Type nvarchar(128)) INSERT INTO @ReqType (Type) SELECT DISTINCT Tree.Type as 'Requirement Type' FROM [TREE] INNER JOIN [Project_INFO] ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] INNER JOIN [Baseline_INFO] ON [Baseline_INFO].[BaselineID]=[Tree].[Baseline_ID] WHERE [Project_INFO].[Name] = 'Address Book' AND [Baseline_INFO].[Name] = 'Current Baseline' Group By Tree.Type SELECT Tree.Type as 'Requirement Type', COUNT(Tree.Type) as 'Number in Creation Range' FROM [Tree] INNER JOIN @ReqType As RT on RT.Type = Tree.Type INNER JOIN [Project…

Accepted Answer

Modify the second query

```
SELECT Tree.Type as 'Requirement Type',
       COUNT(CASE WHEN [Tree].[creationDate] >= ('2010-01-01') and [Tree].[creationDate] < ('2020-01-01') THEN Tree.Type END) AS 'Number in Creation Range'
FROM [Tree] 
INNER JOIN @ReqType As RT on RT.Type = Tree.Type
INNER JOIN [Project_INFO]  ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] 
INNER JOIN @CreationCount AS CCount ON CCount.BaselineID=Tree.Baseline_ID 
WHERE [Project_INFO].[Name] = 'Address Book' AND CCount.Name = 'Current Baseline' 
GROUP BY tree.Type
```

SQL - Returning all rows even if count is zero for item

Solution

Problem