I really like SQL. It is so compact. So nice results can be achieved with relatively small amount of code. I know, I know … It can be complex puzzle when use with long subqueries or other “modern” SQL features. While I am not SQL master I have such problems too.

But recently I learned new SQL feature. Or if you prefer I can call it ‘trick’. I wanted to count my records by value in one of the fields grouped in series by date. I wanted to get result table as follows:

Month state_a state_b state_c
2017-12-17 10 12 9
2017-12-16 11 9 12
2017-12-15 15 4 10

My first approach to this problem was

SELECT
  created_at::date,
  SUM(CASE WHEN previous_state='a' THEN 1 ELSE 0 END) AS state_a,
  SUM(CASE WHEN previous_state='b' THEN 1 ELSE 0 END) AS state_b,
  SUM(CASE WHEN previous_state='c' THEN 1 ELSE 0 END) AS state_c
FROM undos
GROUP BY 1
ORDER BY 1 DESC

I got the result I expected, so work done. No! I could not resist to find a way to get rid of this not so pretty sum-case-when-then-else-end combo. I would lie if I told that I found solution fast. But it is not story about searching solution … Sorry. Anyway it was not fascinating story. Long story short: try to look for pivoting rows in postgres.

Proper solution you can find below.

SELECT
  created_at::date,
  COUNT(*) FILTER (WHERE previous_state = 'a') AS state_a,
  COUNT(*) FILTER (WHERE previous_state = 'b') AS state_b,
  COUNT(*) FILTER (WHERE previous_state = 'c') AS state_c
FROM undos
GROUP BY 1
ORDER BY 1 DESC;

Looks like FILTER is the right tool here. FILTER is extending aggregating function with extra condition that make COUNT works only with records for which WHERE predicate is true. It is good at pivoting rows to columns and probably couple more abut which I have no idea. In case of performance query plan is the same for both queries.FILTER is part of SQL:2003 standard — so it is not new, cool stuff. One the other hand FILTER does not have big adoption. As far as I know only Postgresql has adopted it in version 9.4 released in 2014.

I encourage every programmer to learn SQL and try to use it for generating some reports. I believe that SQL skill will make better from any of you. Maybe some 2018 resolution?