select and count based on a condition I have two tables, proj: id name and proj_reports: id, position, created_at, key_id, proj_id and
I want to select from key_reports for each proj_id:

- total number of `key_id` which can be done with `count(.key_id)`
- total number of DISTINCT `key_id` `count(DISTINCT .key_id)`
- total number of `key_id` where `position` &gt; 1
- total number of `key_id` where `position` &gt; 2 AND &lt; 5


The problem that I have is that I don't know how to add those conditions for checking if the position is > or &lt; a number or in a range.
You can use a CASE expression inside the COUNT aggregate funciton.

SELECT
    COUNT( CASE WHEN position &gt; 1 THEN key_id ELSE NULL END ) AS GT1,
    COUNT( CASE WHEN position &gt; 2 AND position &lt; 5 THEN key_id ELSE NULL END ) AS GT2LT5,
FROM
     proj_reports


The COUNT aggregate does not count NULL values, so that's why it works. 

Here is a SQL Fiddle that demonstrates the concept.