title: SQL,multiple counts with multiple results
body: <p>I am trying to do multiple counts in a single sql statement.</p>
  
  <p>I have two people, Mark and Chris.</p>
  
  <p>I want to count how many times each takes the train on a certain date. Here is the code I am using.</p>
  
  <pre><code>SELECT TO_DATE(TRAIN.DEPARTURE_DATE,'YYYYMM')
    , (select COUNT(DISTINCT DEPARTURE_DATE) 
       FROM TRAIN 
       WHERE PERSON_ID='28' AND DEPARTURE_STATION = 'DUBLIN') AS Mark
    , (select COUNT(DISTINCT DEPARTURE_DATE) 
       FROM TRAIN 
       WHERE PERSON_ID='29' AND DEPARTURE_STATION = 'DUBLIN') AS Chris 
  FROM TRAIN 
  GROUP BY DEPARTURE_DATE
  </code></pre>
  
  <p>The format this code produces is correct, however the result is not. The result is</p>
  
  <pre><code>TO_DATE      Mark       Chris
  2009-01-01     8          11
  2009-01-02     8          11
  2009-01-03     8          11
  </code></pre>
  
  <p>etc....</p>
  
  <p>The correct result should</p>
  
  <pre><code>TO_DATE      Mark       Chris
  2009-01-01     8          11
  2009-01-02     3          7
  2009-01-03     6          5
  </code></pre>
  
  <p>etc...</p>
  
  <p>Can anyone see the problem with my code?</p>
  
  <p>All help is appreciated</p>