CODEWITHSUNDEEP
sqlsql-serverview
proPhet
proPhet

Reputation: 1228

CREATE VIEW with multiple tables - must show 0 values

I have three tables:

// priorities       // statuses              // projects
+----+--------+     +----+-------------+     +----+------+--------+----------+
| ID | NAME   |     | ID | STATUS NAME |     | ID | NAME | STATUS | PRIORITY |
+----+--------+     +----+-------------+     +----+------+--------+----------+
| 1  | Normal |     | 1  | Pending     |     | 1  | a    | 1      | 3        |
+----+--------+     +----+-------------+     +----+------+--------+----------+
| 2  | High   |     | 2  | In Progress |     | 2  | b    | 1      | 1        |
+----+--------+     +----+-------------+     +----+------+--------+----------+
| 3  | Urgent |                              | 3  | c    | 2      | 1        |
+----+--------+                              +----+------+--------+----------+
                                             | 4  | d    | 1      | 2        |
                                             +----+------+--------+----------+

I need to create a view that shows how many projects hold a status of 1 and a priority of 1, how many hold a status of 1 and a priority of 2, how many hold a status of 1 and a priority of 3, and so on.

This should go through each status, then each priority, then count the projects that apply to the criteria.

The view should hold values something like this:

// VIEW (stats)
+--------+----------+-------+
| STATUS | PRIORITY | COUNT |
+--------+----------+-------+
| 1      | 1        | 1     |
+--------+----------+-------+
| 1      | 2        | 1     |
+--------+----------+-------+
| 1      | 3        | 1     |
+--------+----------+-------+
| 2      | 1        | 1     |
+--------+----------+-------+
| 2      | 2        | 0     |
+--------+----------+-------+
| 2      | 3        | 0     |
+--------+----------+-------+

This view is so that I can call, for example, how many projects have a status of 1 and a priority of 3, the answer given the data above should be 1.

Using the below select statement I've been able to produce a similar result but it does not explicitly show that 0 projects have a status of 2 and a priority of 3. I need this 0 value to be accessible the same way as any of the others with a COUNT >= 1.

// my current select statement

CREATE VIEW stats
AS
SELECT P.STATUS, P.PRIORITY, COUNT(*) AS hits
FROM projects P
GROUP BY P.STATUS, P.PRIORITY

// does not show rows where COUNT = 0

How could I create a VIEW that holds all of the priorities' ids, all of the statuses' ids, and 0 values for COUNT?

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to generate all the rows and then get the count for each one. Here is a query that should work:

SELECT s.status, p.priority, COUNT(pr.status) AS hits
FROM (SELECT DISTINCT status FROM projects) s CROSS JOIN
     (SELECT DISTINCT priority FROM projects) p LEFT JOIN
     project pr
     ON pr.status = s.status and pr.priority = p.priority
GROUP BY s.status, p.priority;

Upvotes: 1

Related Questions