2

I have this query and works. But It seems ugly to me that it needs some optimization since I repeat the same actions over and over again. There must be a better way to merge all of these

SELECT *
FROM
  (SELECT COUNT(*) AS a
   FROM order_item_histories
   WHERE order_status_id IN (5, 4, 15)) AS e,
  (SELECT COUNT(*) AS b
   FROM order_item_histories
   WHERE order_status_id IN (6)) AS f,
  (SELECT COUNT(*) AS c
   FROM order_item_histories
   WHERE order_status_id IN (4, 10, 9, 12, 7)) AS g,
  (SELECT COUNT(*) AS d
   FROM order_item_histories
   WHERE order_status_id IN (11, 16, 17, 13)) AS h
6

Use conditional aggregation:

select count(*) filter (where order_status_id in (5,4,15)) as a, 
       count(*) filter (where order_status_id in (6)) as b,
       count(*) filter (where order_status_id in (4,10,9,12,7)) as c,
       count(*) filter (where order_status_id in (11,16,17,13)) as d
from order_item_histories;
  • Great solution thx. However, it would be amazing if we could somehow unify count(*) since all of them shares it. – SNaRe Apr 3 '18 at 19:27
  • 2
    @SNaRe: that's as compact as it gets. – a_horse_with_no_name Apr 3 '18 at 19:32

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