I've got a table with records that have some properties. One of the properties is their state.
I also have a view that counts and groups the states. It looks like this:
State Total
Canceled 9
Failed 3
Pending 10
Succeeded 7
Since there is no record with the 'Processing' state in my reference table, the view doesn't show it in the view. However, I want each of the five possible states in my view, even if it doesn't exist in the table.
I made the following query and am close to getting it to work (testing it with one of the available states):
SELECT State, COUNT(State) AS Total
FROM dbo.Job
GROUP BY State
UNION
SELECT 'Processing' AS State, 0 AS Total
The problem is my '0 AS Total'. The 0 should be dynamic. Is there a way I can replace it?
In order to get a comprehensive list of State, all of the values have to come from somewhere other than your starting data set (assuming that not all values are represented, as is the current case with 'Processing'). That can be a table, which is your best option if you have the privileges to create such a table, or, if need be, from a list, which you'll then have to maintain if new State values are added.
In the second case, a table value constructor will do the job for you. Start with that constructor, then LEFT JOIN to your data source. An INNER JOIN will, again, filter off values missing in your underlying data.
SELECT
s.[State]
,COUNT(j.[State]) AS Total
FROM
(
VALUES
('Canceled')
,('Failed')
,('Pending')
,('Succeeded')
,('Processing')
) AS s ([State])
LEFT JOIN
dbo.Job AS j
ON
s.[State] = j.[State]
GROUP BY
s.[State];
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments