LEFT JOIN not showing days with zero COUNT

I am trying to retrieve data from the last X days, counting the new rows per day. I need to get also the days that have COUNT = 0.

My query right now is:

SELECT COUNT(*), CAST(TIMESTAMP_T AS DATE) 
FROM TABLE_A a 
LEFT OUTER JOIN
     (SELECT CAST(CALENDAR_DATE AS DATE) as DATE0 
      FROM   SYS_CALENDAR.CALENDAR) b 
ON       CAST(a.TIMESTAMP_T AS DATE)  =  b.DATE0 
WHERE    b.DATE0 BETWEEN CURRENT_DATE - INTERVAL '7' DAY AND CURRENT_DATE 
GROUP BY CAST(a.TIMESTAMP_T AS DATE) 
ORDER BY CAST(a.TIMESTAMP_T AS DATE) DESC

It is returning the following:

255 2019-11-07
922 2019-11-06
342 2019-11-05
176 2019-11-04
18  2019-11-02
224 2019-11-01

As you can see, there are days that are not being displayed by the query.

Answer

You should be able to simplify it like so:

SELECT COUNT(a.TIMESTAMP_T), b.CALENDAR_DATE
FROM SYS_CALENDAR.CALENDAR b
LEFT JOIN TABLE_A a ON CAST(a.TIMESTAMP_T AS DATE) = b.CALENDAR_DATE
WHERE b.CALENDAR_DATE BETWEEN CURRENT_DATE - INTERVAL '7' DAY AND CURRENT_DATE 
GROUP BY b.CALENDAR_DATE
ORDER BY b.CALENDAR_DATE DESC

A couple notes:

• the calendar_date field in the sys_calendar.calendar view is DATE, no need to cast it
• Teradata lets you reference aliased columns without having to do an outer SELECT
• replaced COUNT(*) with COUNT(a.TIMESTAMP_T) for a more accurate count (thanks jarlh)