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.


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

    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,

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

Here is a SQL Fiddle that demonstrates the concept.

  • how can I count the position for distinct key_id, COUNT( CASE WHEN position > 1 THEN key_id ELSE NULL END ) AS GT1 where key_id DISTINCT? Thank you for your time:) – RockNinja Apr 3 '14 at 15:03
  • COUNT( DISTINCT CASE ... ) – efesar Apr 3 '14 at 15:11
  • I tried that, but is not giving the right result, edit: because I need to count the distinct key_id with only one position, because a key_id can have multiple positions. – RockNinja Apr 3 '14 at 15:35
  • Can you post your table or a table that represents an example of the data? – efesar Apr 3 '14 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.