Archive for the ‘Database’ Category

How to use database aggregate functions with condition (PostgreSQL) ?

Have you ever needed summing the column data, but not all of it – but depending on another column? You can think about using WHERE in the query. For example:

SELECT SUM(amount) 
  FROM items
     WHERE amount > 5

And it should work. But what if you need sum two times?

SELECT SUM(amount) AS more_than_5, SUM(amount) AS all
  FROM items
     WHERE amount > 5

This time WHERE keyword will not help us – we cannot differentiate.

What is the solution?

I will show practical example:

   SUM( CASE WHEN cards_status_id = 4 THEN stake_amount
                  ELSE 0
           END) AS cancel_amount
       FROM cards

In this table there were card records. And I need cards with status canceled (id 4) and I needed also count of all cards (which are not selected this time). The example with CASE can give what we want without using WHERE keyword.

So the CASE gives all rows to the SUM function but, ones are with value ‘stake_amount’, others are with value 0. SUM function takes all rows because we did not use WHERE keyword but adding 0 will do the magic :)

I am not sure how it will work with other databases, but it works on PostgreSQL.