StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query to count rows under 3 different conditions
text
Body
copied!<p>So I want to create a single query which will return me names of <code>projects</code> and number of <code>assigments</code> which are related to <code>project</code> in particular severity. For example: table <code>assigment</code> contains:</p> <pre><code> 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| </code></pre> <p>Table <code>project</code> contains only <code>name</code> and <code>id</code> as follows:</p> <pre><code> id | name -----+------------------------------------- 1 | Very Important Project </code></pre> <p>I would like to create a single query which will return something like this:</p> <pre><code>projectid | projectname | CriticalAssig| MediumAssig | LowAssig ------------+-----------------------+--------------+-------------+---------- </code></pre> <p>This works for me at the moment:</p> <pre><code>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; </code></pre> <p>But result of this query is ridiculous in columns <code>one</code>, <code>two</code>, <code>three</code> I get numbers like <code>90000</code> (the same number everywhere) while the simple query <code>select count(*) from assig where project_id=x</code> returns <code>300</code>.</p> <p>Can anyone point me where my mistake is located?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload