SQL Server Trying to insert a count of zero when a record doesn't exist

2597 views sql-server
1

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:

enter image description here

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:

enter image description here

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?

answered question

Look into cross join.. what you could do is generate a cte with all of the status and location combinations with a count of 0.. then run your query to get the result and then cross join to the CTE.. this will give you duplicate line for everything, even if returned by your query... then you simply aggregate your resulting data.. makes sense?

2 Answers

3

You have the join backwards.

You left join against the subset. (Or do it the way you have it and RIGHT OUTER JOIN, except no one really uses right joins)

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

posted this
4

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

posted this

Have an answer?

JD

Please login first before posting an answer.