SQL,multiple counts with multiple results

Question

I am trying to do multiple counts in a single sql statement.

I have two people, Mark and Chris.

I want to count how many times each takes the train on a certain date. Here is the code I am using.

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

The format this code produces is correct, however the result is not. The result is

TO_DATE      Mark       Chris
2009-01-01     8          11
2009-01-02     8          11
2009-01-03     8          11

etc....

The correct result should

TO_DATE      Mark       Chris
2009-01-01     8          11
2009-01-02     3          7
2009-01-03     6          5

etc...

Can anyone see the problem with my code?

All help is appreciated

Answer 1

Try this:

SELECT TO_DATE(TRAIN.DEPARTURE_DATE,'YYYYMM')
  , (select COUNT(*) 
     FROM TRAIN 
     WHERE PERSON_ID='28' AND DEPARTURE_STATION = 'DUBLIN'
     AND DepartureDate = t.DepartureDate) AS Mark
  , (select COUNT(*) 
     FROM TRAIN 
     WHERE PERSON_ID='29' AND DEPARTURE_STATION = 'DUBLIN' 
     AND DepartureDate = t.DepartureDate) AS Chris 
FROM TRAIN t
GROUP BY DEPARTURE_DATE

Answer 2

You need one more condition in the subqueries:

AND TO_DATE(ti.DEPARTURE_DATE,'YYYYMM') = TO_DATE(to.DEPARTURE_DATE,'YYYYMM')

SELECT TO_DATE(TRAIN.DEPARTURE_DATE,'YYYYMM')
  , (select COUNT(DISTINCT DEPARTURE_DATE) 
     FROM TRAIN ti
     WHERE PERSON_ID='28' AND DEPARTURE_STATION = 'DUBLIN' AND TO_DATE(ti.DEPARTURE_DATE,'YYYYMM') = TO_DATE(to.DEPARTURE_DATE,'YYYYMM')) AS Mark
  , (select COUNT(DISTINCT DEPARTURE_DATE) 
     FROM TRAIN 
     WHERE PERSON_ID='29' AND DEPARTURE_STATION = 'DUBLIN' AND TO_DATE(ti.DEPARTURE_DATE,'YYYYMM') = TO_DATE(to.DEPARTURE_DATE,'YYYYMM')) AS Chris 
FROM TRAIN to
GROUP BY DEPARTURE_DATE

Answer 3

You don't need multiple correlated sub queries here and can use a PIVOT technique instead.

SELECT TO_DATE(TRAIN.DEPARTURE_DATE, 'YYYYMM'),
       SUM(CASE
             WHEN person_id = 28 THEN 1
             ELSE 0
           END) Mark,
       SUM(CASE
             WHEN person_id = 29 THEN 1
             ELSE 0
           END) Chris
FROM   TRAIN
WHERE  DEPARTURE_STATION = 'DUBLIN'
       AND person_id IN ( 28, 29 )
GROUP  BY TO_DATE(TRAIN.DEPARTURE_DATE, 'YYYYMM')