title: MySQL, DISTINCT in SUM operation
body: <p>Currently I trying to calculate number of unique user visit in my application based on user gender. Here is the example query that calculate all the visits (not unique)</p>
  
  <pre><code>SELECT
      DATE(v.visited_at) AS visit_date,
      SUM(IF(u.gender = 'M', 1, 0)) AS male_visit,
      SUM(IF(u.gender = 'F', 1, 0)) AS female_visit,
      SUM(IF(u.gender = '' OR u.gender IS NULL, 1, 0)) AS unknown_visit
  FROM 
      visits v
      INNER JOIN users u ON v.user_id = u.id
  WHERE
      DATE(v.visited_at) &gt;= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
      AND v.duration &gt; 30
  GROUP BY
      DATE(v.visited_at)
  </code></pre>
  
  <p>Tried using subquery and count distinct it's works, but it's 4 times slower.</p>
  
  <pre><code>SELECT
      DATE(visited_at) as visit_date,
      (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = 'M' AND DATE(v.visited_at) = visit_date AND v.duration &gt; 30) AS male_visit,
      (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = 'F' AND DATE(v.visited_at) = visit_date AND v.duration &gt; 30) AS female_visit,
      (SELECT COUNT(DISTINCT u.id) FROM visits v JOIN users u ON v.user_id = u.id WHERE u.gender = '' OR u.gender IS NULL AND DATE(v.visited_at) = visit_date AND v.duration &gt; 30) AS unknown_visit
  FROM 
      visits v
  WHERE
      DATE(visited_at) &gt;= DATE_SUB(SYSDATE(), INTERVAL 30 DAY)
  GROUP BY
      DATE(visited_at)
  </code></pre>
  
  <p>Any suggestion on this?</p>