title: SQL using CASE in count and group by
body: <p>I'm using <code>CASE</code> to categorize data in the table and count them but the results aren't accurate  </p>
  
  <p>live demo <a href="http://sqlfiddle.com/#!2/f1f44/8" rel="noreferrer">[here]</a></p>
  
  <pre><code>select DATE(date) as day, count(*),
  count(distinct case when name = 'fruit' then 1 else 0 end) as fruits,
  count(distinct case when name = 'vege' then 1 else 0 end) as vege,
  count(distinct case when name = 'sweets' then 1 else 0 end) as sweets
  from food
  group by day
  with rollup
  </code></pre>
  
  <p>I'm not sure if the issue is with <code>CASE</code> or in the string matching <code>=</code> because there's no 'sweets' still it counts 1?
  any pointers I'd be grateful </p>