title: GROUP BY + CASE statement
body: <p>I have a working query that is grouping data by hardware model and a result, but the problem is there are many <em>"results"</em>.  I have tried to reduce that down to <em>"if result = 0  then keep as 0, else set it to 1"</em>.  This generally works, but I end up having:</p>
  
  <pre><code>    day     |      name      | type | case | count
  ------------+----------------+------+------+-------
   2013-11-06 | modelA         |    1 |    0 |   972
   2013-11-06 | modelA         |    1 |    1 |    42
   2013-11-06 | modelA         |    1 |    1 |     2
   2013-11-06 | modelA         |    1 |    1 |    11
   2013-11-06 | modelB         |    1 |    0 |   456
   2013-11-06 | modelB         |    1 |    1 |    16
   2013-11-06 | modelB         |    1 |    1 |     8
   2013-11-06 | modelB         |    3 |    0 | 21518
   2013-11-06 | modelB         |    3 |    1 |     5
   2013-11-06 | modelB         |    3 |    1 |     7
   2013-11-06 | modelB         |    3 |    1 |   563
  </code></pre>
  
  <p>Instead of the aggregate I am trying to achieve, where only 1 row per type/case combo.</p>
  
  <pre><code>    day     |      name      | type | case | count
  ------------+----------------+------+------+-------
   2013-11-06 | modelA         |    1 |    0 |   972
   2013-11-06 | modelA         |    1 |    1 |    55
   2013-11-06 | modelB         |    1 |    0 |   456
   2013-11-06 | modelB         |    1 |    1 |    24
   2013-11-06 | modelB         |    3 |    0 | 21518
   2013-11-06 | modelB         |    3 |    1 |   575
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>select CURRENT_DATE-1 AS day, model.name, attempt.type, 
         CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END, 
         count(*) 
  from attempt attempt, prod_hw_id prod_hw_id, model model
  where time &gt;= '2013-11-06 00:00:00'  
  AND time &lt; '2013-11-07 00:00:00'
  AND attempt.hard_id = prod_hw_id.hard_id
  AND prod_hw_id.model_id = model.model_id
  group by model.name, attempt.type, attempt.result
  order by model.name, attempt.type, attempt.result;
  </code></pre>
  
  <p>Any tips on how I can achieve this would be awesome.</p>
  
  <p>Day will always be defined in the <code>WHERE</code> clause, so it will not vary. <code>name, type, result(case)</code> and <code>count</code> will vary. In short, for any given model I want only 1 row per <em>"type + case"</em> combo. As you can see in the first result set I have 3 rows for <code>modelA</code> that have <code>type=1</code> and <code>case=1</code> (because there are many <em>"result"</em> values that I have turned into <em>0=0 and anything else=1</em>). I want that to be represented as 1 row with the count aggregated as in example data set 2.</p>