select and count based on a condition

Question

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` > 1
- total number of `key_id` where `position` > 2 AND < 5

The problem that I have is that I don't know how to add those conditions for checking if the position is > or < a number or in a range.

Answer 1

You can use a CASE expression inside the COUNT aggregate funciton.

SELECT
    COUNT( CASE WHEN position > 1 THEN key_id ELSE NULL END ) AS GT1,
    COUNT( CASE WHEN position > 2 AND position < 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.