title: Refactor SQL for speed: use CASE instead UNION ALL
body: <p>Table T(user_id, bucket,  profile, money, model, type).</p>
  <p>All columns: &quot;bucket&quot;, &quot;model&quot; and &quot;type&quot; are having low cardinality (&lt;5)</p>
  <p>I need to calculate several aggregates: count(distinct user_id), sum(money)
  grouped by following criteria:</p>
  <pre><code>1) group by bucket, model
  2) group by bucket, model, type
  3) group by bucket where type='A'
  4) group by bucket where profile='adult'
  </code></pre>
  <p>and return it as a single result.</p>
  <p>My current approach is:</p>
  <pre><code>select bucket, model, NULL as type, count(distinct user_id), sum(money), 'model' as helper from T 
  group by bucket, model
  UNION ALL
  select bucket, model, type, count(distinct user_id), sum(money), 'model_type' as helper from T 
  group by bucket, model, type
  UNION ALL
  select bucket, NULL as model, 'A' as type, count(distinct user_id), sum(money), 'type_A' as helper from T  
  where type='A' group by bucket 
  UNION ALL
  select bucket, NULL as model, NULL as type, count(distinct user_id), sum(money), 'profile_adult' as helper 
  from T  where profile='adult' group by bucket 
    
  </code></pre>
  <p>The source table is big (millions of rows, Redshift) and the query is slow.</p>
  <p>Every subquery above returns less than 10 rows.</p>
  <p>I think it is slow because it is scanned 4 times ( 4 unions in SQL).</p>
  <p>Is it possible to achieve it in single table scan?
  Since it Redshift, adding indexes is not a solution.</p>
  <p>I was thinking about using CASE operator, but cannot figure how to make it work here.</p>