Wednesday, November 24, 2010

Shift the condition to fields

The report needs to sum() fields in opposite condition. Example: sum(salary) where employee id > 500 and sum(salary) where employee id <=500. Below is how the example report looked like.

Department, Total salary where id > 500; Total salary where id <= 500
Account, 10000, 20000
IT, 20000, 10000

Usually, we would go for sub-query to solve the above problem such as (or putting the sub-query in the FROM clause and use a join):

SELECT emp.dept, (SELECT SUM(emp1.salary) FROM emp1 WHERE emp1.id > 500 AND emp1.dept = emp.dept), (SELECT SUM(emp2.salary) FROM emp2 WHERE emp2.id >= 500 AND emp2.dept = emp.dept)
FROM emp GROUP BY dept

Now, there is another way to generate the same result without using sub-query by using decode (matching character condition) or case (true or false condition)

SELECT emp.dept, SUM(CASE WHEN emp.id > 500 THEN emp.salary ELSE 0 END), SUM(CASE WHEN emp.id<=500 THEN emp.salary ELSE 0 END)
FROM emp GROUP BY dept

So, the sum will only do its work when the case condition is true and thus, eliminate the need sub-query. The performance are better using this way and reduce the cost for additional table access.