sql-server - SQL Server : trying to insert a count of zero when a record doesn't exist

Question

I am trying to modify the results of a query to populate a zero when a certain status doesn't exist.

In my base result I have something that looks like this:

But when a certain example doesn't appear in my table, I need a way to have a row show up with a zero for reporting needs, something like this:

I was trying to use a CTE maybe to populate those and left join it up...but doesn't seem to be working the way I want.

WITH DummyValues AS 
(
    SELECT 'Yellow' AS Val
    UNION ALL
    SELECT 'Red'
    UNION ALL
    SELECT 'Gray'
) 
SELECT D.Val, V.PlntCd, COUNT(UpgradeMeasure)  
FROM reporting.vw_SOTAgingView V
LEFT OUTER JOIN DummyValues D ON D.Val = V.UpgradeMeasure
GROUP BY D.Val, V.PlntCd

Is this an easy thing I am just missing something simple?

标签： sql-server

Answer 1

你有向后的连接。

您离开了对子集的联接。（或者按照你拥有的方式和 RIGHT OUTER JOIN 进行操作，除非没有人真正使用右连接）

SELECT
    *
FROM
    TableWithAllData All
    LEFT JOIN TableWithSomeData Some ON Some.Id = All.id