SQL query to count rows under 3 different conditions

Question

So I want to create a single query which will return me names of projects and number of assigments which are related to project in particular severity. For example: table assigment contains:

 id  |                name                 | project_id  | severity  | 
-----+-------------------------------------+-------------+-----------+
 148 | Create background                   |         1   |          1|  
 184 | Create frontend                     |         1   |          1|  
 151 | Create Budged                       |         1   |          2|  
 155 | Assign all tasks                    |         1   |          3|    
 179 | Drink Beer                          |         1   |          1|   

Table project contains only name and id as follows:

 id  |                name                 
-----+-------------------------------------
 1   | Very Important Project

I would like to create a single query which will return something like this:

projectid   |  projectname          | CriticalAssig| MediumAssig | LowAssig
------------+-----------------------+--------------+-------------+----------

This works for me at the moment:

SELECT p.id, 
       p.name, 
       Count(a1.id)  AS one, 
       Count(a2.id)  AS two, 
       Count (a3.id) AS three 
FROM   project p 
       INNER JOIN assign a1 
               ON a1.project_id = p.id 
       INNER JOIN assign a2 
               ON a2.project_id = p.id 
       INNER JOIN assign a3 
               ON a3.project_id = p.id 
WHERE  a2.severity = '2' 
       AND a1.severity = '1' 
       AND a3.severity = '3' 
GROUP  BY p.id, 
          p.name; 

But result of this query is ridiculous in columns one, two, three I get numbers like 90000 (the same number everywhere) while the simple query select count(*) from assig where project_id=x returns 300.

Can anyone point me where my mistake is located?

Answer 1

You can do it without any joins:

count(case when severity = 1 then 1 else null end) as one

(The problem in your current query is that you're multiplying the number of rows before grouping. When developing aggregates, run the query without the aggregate function calls and group by/having clauses, to see what you're actually counting, summing, etc.)

Answer 2

Try this:

SELECT p.id, p.name, 
       SUM(a.severity = 1) AS one, 
       SUM(a.severity = 2) AS two, 
       SUM(a.severity = 3) AS three
FROM project p 
INNER JOIN assign a ON a.project_id = p.id  
GROUP BY p.id, p.name

OR

SELECT p.id, p.name, 
       SUM(CASE WHEN a.severity = 1 THEN 1 ELSE 0 END) AS one, 
       SUM(CASE WHEN a.severity = 2 THEN 1 ELSE 0 END) AS two, 
       SUM(CASE WHEN a.severity = 3 THEN 1 ELSE 0 END) AS three
FROM project p 
INNER JOIN assign a ON a.project_id = p.id  
GROUP BY p.id, p.name