T-SQL Query will not return 0 value for a Sum

Question

I have a query where I'm calculating Days of Therapy for medications. I want to have 0 values to show for months that have no data. Currently the query returns no record if the Sum is 0. Can't seem to figure this out. See the Query Below:

If I were to comment out identifiers related to the DOT_ALL table along with the Where Clause I get 60 rows, 1 for each month for the past 5 years. However, otherwise i get only 57 for the drug in the Where Clause since there are not DOTs for Aug 2016, April 2016 and Jan 2015.

Thanks in advance.

----------------------------------------------------------------------------

SELECT
    AMS.[Medication Name]
    , SUM(AMS.DOT) AS DOT
    ,  PD.[Patient Days]
    , PD.[Month_Name]
    , PD.[Fiscal_Month]
    , PD.[Accounting_Year]
    , PD.[Year]

FROM
    DW_PROD.dbo.Patient_Days_By_Month PD 
    Left JOIN [DW_PROD].[dbo].[DOTS_All] AMS ON (PD.Month_Name = AMS.Month AND PD.Year = AMS.Year)

WHERE
    [Medication Name] = 'CEFUROXIME'

GROUP BY
    AMS.[Medication Name]
    , PD.[Patient Days]
    , PD.[Month_Name]
    , PD.[Fiscal_Month]
    , PD.[Accounting_Year]
    , PD.[Year]

ORDER BY
    ACCOUNTING_YEAR
    ,FISCAL_MONTH

`Medication Name` will be a column of `DOTS_All` (?), and it is in the `WHERE` so it will restrict the whole resultset. Try adding it as an additional join condition instead. — Cee McSharpface, Jan 30 '17 at 23:32
Potential dup: http://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate — D.N., Jan 30 '17 at 23:32
Use a table for the dates (cte, subquery, hardcoded, whatever) and left join both tables from that. — Malk, Jan 30 '17 at 23:34
[This](http://sqlmag.com/sql-server/logical-query-processing-what-it-and-what-it-means-you) article may help you understand the logical order in which query elements are processed. Since your `WHERE` clause eliminates some months, and it is processed before the `SELECT` and, hence, aggregation, you won't get rows for those months. Using a _numbers_ or _tally_ table to provide the desired range of months and `OUTER JOIN` to add your data will let you achieve the desired result. — HABO, Jan 31 '17 at 01:14

score 2 · Accepted Answer · edited May 23 '17 at 11:53

This may be the cheapest solution, under the assumption that Patient_Days_By_Month is already some kind of calendar.

SELECT
    'CEFUROXIME' AS [Medication Name],
    SUM(AMS.DOT) AS DOT,
    PD.[Patient Days],
    PD.[Month_Name],
    PD.[Fiscal_Month],
    PD.[Accounting_Year],
    PD.[Year]
FROM DW_PROD.dbo.Patient_Days_By_Month PD 
LEFT JOIN [DW_PROD].[dbo].[DOTS_All] AMS 
    ON PD.Month_Name = AMS.Month 
    AND PD.Year = AMS.Year
    AND [Medication Name] = 'CEFUROXIME'
GROUP BY
    PD.[Patient Days],
    PD.[Month_Name],
    PD.[Fiscal_Month],
    PD.[Accounting_Year],
    PD.[Year]
ORDER BY
    PD.ACCOUNTING_YEAR,
    PD.FISCAL_MONTH

JOIN conditions do not need to refer to columns in other tables, they can as well contain constants.

The medication name was originally restricted in the WHERE clause - that eliminated all non-cefuroxime records from the resultset.

T-SQL Query will not return 0 value for a Sum

1 Answers1