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?
You can use a
LEFT OUTER JOIN like this to always include the statuses (I switched the order of the tables since that is usually easier to read for most people):
SELECT D.Val, V.PlntCd, COALESCE(COUNT(UpgradeMeasure), 0) AS [Count] FROM (SELECT 'Yellow' UNION ALL SELECT 'Red' UNION ALL SELECT 'Gray') D LEFT OUTER JOIN reporting.vw_SOTAgingView V ON D.Val = V.UpgradeMeasure GROUP BY D.Val, V.PlntCd
Just note that this won't exactly get your desired set. The "PlntCd" will be NULL if no match is found. If you want to ensure you cover all your plants, you need to start with a complete listing of plants and
CROSS JOIN that source to statuses first. This might look like:
SELECT D.Val, -- From cross-join P.PlntCd, -- From source COALESCE(COUNT(UpgradeMeasure), 0) AS [Count] FROM (SELECT DISTINCT PlntCd FROM reporting.vw_SOTAgingView) P CROSS JOIN (SELECT 'Yellow' UNION ALL SELECT 'Red' UNION ALL SELECT 'Gray') D LEFT OUTER JOIN reporting.vw_SOTAgingView V ON D.Val = V.UpgradeMeasure AND P.PlntCd = V.PlntCd -- Also join to source to prevent dupes GROUP BY D.Val, P.PlntCd -- Use source plant code