Returning zero when no matching rows exist

user_8040537 0
Viewed 30

I have a table containing three columns: Age (bucket), Date of Registration & Numbers Registered. Currently, only rows exist where there are registration for that date and age bucket. There are currently 11 age buckets and 14 dates. Therefore I'd like the output to have 154 rows (11 x 14). The SQL Code I have is:

SELECT DISTINCT Age INTO #AgesTable FROM #Output
SELECT DISTINCT DateRegistered INTO #DatesTable FROM #Output

SELECT
a.Age
, d.DateRegistered
, COALESCE(o.Registrations, 0) AS Registrations
FROM #Output o
FULL OUTER JOIN #AgesTable a ON o.Age = a.Age
FULL OUTER JOIN #DatesTable d on o.DateRegistered = d.DateRegistered
WHERE a.Age = '''80+'

.... or have I gone about this the wrong way?

sql
1 Answers

I think you want to cross join a & d then left join o.

;WITH CTE_AGE AS
(
  SELECT DISTINCT Age 
  FROM #Output
)
, CTE_DATES AS
(
  SELECT DISTINCT DateRegistered
  FROM #Output
)
SELECT a.Age, d.DateRegistered
, COALESCE(o.Registrations, 0) AS Registrations
FROM CTE_AGE a 
CROSS JOIN CTE_DATES d 
LEFT JOIN #Output o
  ON o.Age = a.Age
 AND o.DateRegistered = d.DateRegistered
WHERE a.Age LIKE '%80+'
user_40034190
Related