SQL,multiple counts with multiple results

Question

Hi folks,

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