title: MySQL Subquery Count Using DATE_FORMAT alias
body: <p>I've been banging my head against the wall for the past hours trying to get this single query to work but having no success.</p>
  
  <p>I have a single table called viewer_log.</p>
  
  <p>Each row is a pageview with a session_id and timestamp attached to it.</p>
  
  <p>I am having troubles formatting the timestamp to a day, grouping by that day, but then counting the number of UNIQUE sessions per day.</p>
  
  <p>So far, for that query (which also took a long time to get working) is:</p>
  
  <pre><code>SELECT day, COUNT(*) as unique_sessions from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS day FROM viewer_log GROUP BY `viewer_session_id` HAVING day='2011-03-23') AS sessions;
  </code></pre>
  
  <p>You will notice the day is hardcoded, giving</p>
  
  <pre><code>mysql&gt; SELECT day, COUNT(*) as unique_sessions from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS day FROM viewer_log GROUP BY `viewer_session_id` HAVING day='2011-03-23') AS sessions;
  +------------+-----------------+
  | day        | unique_sessions |
  +------------+-----------------+
  | 2011-03-23 |              21 |
  +------------+-----------------+
  </code></pre>
  
  <p>I would like to expand these results without having to specify the day, which is where I am running into problems and not able to find an answer on the internet... I'm sure it's out there, not sure what I'm looking for.</p>
  
  <p>EG:</p>
  
  <pre><code>+------------+-----------------+
  | day        | unique_sessions |
  +------------+-----------------+
  | 2011-03-21 |              14 |
  | 2011-03-22 |              11 |
  | 2011-03-23 |              21 |
  +------------+-----------------+
  </code></pre>
  
  <p>I am having lots of trouble moving past this point, I have tried joining the table with a copy of its self but usually run into an unknown column name OR the results I THINK should be showing up are wrong:</p>
  
  <pre><code>mysql&gt; select DATE_FORMAT(v.timestamp, "%Y-%m-%d") AS date, count(sessions) as rows from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS date FROM viewer_log GROUP BY viewer_session_id) AS s JOIN viewer_log v ON (s.date=date) GROUP BY date;
  
  +------------+--------+
  | date       | rows   |
  +------------+--------+
  | 0000-00-00 |   2551 |
  | 0000-00-00 |  20408 |
  | 0000-00-00 |  20408 |
  | 0000-00-00 |   5102 |
  | 0000-00-00 |   2551 |
  | 0000-00-00 |   2551 |
  | 0000-00-00 |  63775 |
  | 0000-00-00 | 211733 |
  | 0000-00-00 |  53571 |
  | 0000-00-00 |  53571 |
  | 0000-00-00 |  53571 |
  | 0000-00-00 |  51020 |
  | 0000-00-00 |  48469 |
  | 0000-00-00 |  91836 |
  | 0000-00-00 |  86734 |
  | 0000-00-00 |  33163 |
  | 0000-00-00 |  45918 |
  | 0000-00-00 |  28061 |
  | 0000-00-00 |  86734 |
  | 0000-00-00 |  76530 |
  | 0000-00-00 |  53571 |
  | 0000-00-00 |  33163 |
  +------------+--------+
  22 rows in set, 1 warning (0.63 sec)
  </code></pre>