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

cjk · Accepted Answer · 2009-06-15 09:53:06Z

4

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

answered Jun 15, 2009 at 9:53

cjk

46.6k9 gold badges83 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dutow · Accepted Answer · 2009-06-15 09:54:47Z

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

answered Jun 15, 2009 at 9:54

Dutow

5,6581 gold badge33 silver badges40 bronze badges

1 Comment

Paul Morgan Over a year ago

will COUNT(DISTINCT DEPARTURE_DATE) only count 1 if you're selecting on DEPARTURE_DATE?

Martin Smith · Accepted Answer · 2013-08-05 09:34:04Z

1

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')

edited Aug 5, 2013 at 9:34

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

answered Jul 31, 2013 at 9:10

joey

111 bronze badge

1 Comment

joey Over a year ago

this is much faster than those previous answers.

Collectives™ on Stack Overflow

SQL,multiple counts with multiple results

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related