CODEWITHSUNDEEP
sqlpostgresqlselectcountgroup-by
Grzzzzzzzzzzzzz
Grzzzzzzzzzzzzz

Reputation: 1341

SQL query to count rows under 3 different conditions

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?

Upvotes: 2

Views: 1112

Answers (4)

Saharsh Shah
Saharsh Shah

Reputation: 29051

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

Upvotes: 3

Ranjit Singh
Ranjit Singh

Reputation: 3735

try this

select p.id, 
p.name, 
count(case when severity = 1 then 1 else null end) as one, 
count(case when severity = 2 then 1 else null end) as two, 
count (case when severity = 3 then 1 else null end) as three
    from project p 
    inner join assign on assign.project_id = p.id 
    group by p.id, p.name;

Upvotes: 2

PeterRing
PeterRing

Reputation: 1797

You need to use subquerys.

select p.id, p.name, 
count(SELECT * FROM project a where severity = 1 and p.id=a.id) as one, 
count(SELECT * FROM project a where severity = 2 and p.id=a.id) as two, 
count(SELECT * FROM project a where severity = 3 and p.id=a.id) as three
from project p
group by p.id, p.name;

Upvotes: 1

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

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.)

Upvotes: 4

Related Questions