Attempting to combine results from two queries-CodePudding

I have two queries:

select
    CM.Month,
    CM.Year
FROM CalendarMonths AS CM

The above query returns all montts and years from available in a Calendar Table.

The second query is as follows:

select
    DATEPART(month, T.Date) AS Month,
    DATEPART(year, T.Date) AS Year,
    ISNULL(SUM(Amount) ,0) As Total
from Transactions T
inner join TransactionClasses TC on TC.TransactionClassId = T.TransactionClassId AND T.TransactionClassId = 3
GROUP BY
    DATEPART(month, T.Date),
    DATEPART(year, T.Date)

This simply returns the the total for Transactions that belong to a specific Transaction Class grouped by month and year. The above query only returns rows for months and years which actually contain transactions.

What I am attempting to accomplish is combining both queries, so that as well returning totals for month and year which contains Transactions, it also returns zero total for the rest of the months.

My attempts so far have been unsuccesfull so any help would be appreciated.

CodePudding user response：

You need to LEFT JOIN to your data set. I would put your second query into a CTE/derived table, and that makes the JOINing easier:

WITH Totals AS(
    SELECT DATEPART(MONTH, T.Date) AS Month,
           DATEPART(YEAR, T.Date) AS Year,
           SUM(TC.Amount) AS Total
    FROM dbo.Transactions T
         INNER JOIN dbo.TransactionClasses TC ON TC.TransactionClassId = T.TransactionClassId
    WHERE T.TransactionClassId = 3 --Moved from ON
    GROUP BY DATEPART(MONTH, T.Date),
             DATEPART(YEAR, T.Date))
SELECT CM.Month,
       CM.Year,
       ISNULL(T.Total,0) AS Total
FROM dbo.CalendarMonths CM
     LEFT JOIN Totals T ON CM.Month = T.Month
                       AND CM.Year = T.Year;