Oracle SELECT : Count of events per team and per year

Question

As said in the title, I want to select the number of events per team and per year. The select statement below works fine but doesn't give me exactly what I am looking for.

SELECT
 Team.team_id,
 TO_CHAR(Event.START_DATE_TIME, 'yyyy') AS year,
 count(event_id)    AS events
FROM
 Team
LEFT OUTER JOIN
 Event ON Event.team_id = Team.team_id
GROUP BY
 TO_CHAR(Event.START_DATE_TIME, 'yyyy'),
 team_id
ORDER BY
 year  ASC,
 team_id  ASC
;

With this, if we have :

Team 1 : 1 event in 2006
Team 2 : 1 event in 2007

We obtain :

ID | Year | Events
------------------
1  | 2006 | 1
2  | 2007 | 1

And I would like to obtain :

ID | Year | Events
-------------------
1  | 2006 | 1
2  | 2006 | 0
1  | 2007 | 0
2  | 2007 | 1

I don't know how to modify my request to do so.

Answer 1

Use:

   SELECT x.team_id,
          x.year,
          COALESCE(COUNT(e.event_id), 0) AS events
     FROM (SELECT :start_year + LEVEL - 1 AS year,
                  t.team_id
             FROM DUAL, TEAM t
       CONNECT BY :start_year + LEVEL - 1 <= :end_year) x
LEFT JOIN EVENT e ON EXTRACT(YEAR FROM e.start_date_time) = x.year
                 AND e.team_id = x.team_id

This will generate a list of years, but you have to set the :start_year and :end_year BIND variables.

Previously:

   SELECT x.team_id,
          x.year,
          COALESCE(COUNT(e.event_id), 0) AS events
     FROM (SELECT 2006 AS year,
                  a.team_id
             FROM TEAM a
           UNION ALL
           SELECT 2007,
                  b.team_id
             FROM TEAM b) x
LEFT JOIN EVENT e ON EXTRACT(YEAR FROM e.start_date_time) = x.year
                 AND e.team_id = x.team_id

TO_CHAR is OK to get the year, but returns it as a CHAR(4) so you need to use TO_NUMBER(TO_CHAR(date_col, 'yyyy')) to get a number. So I used EXTRACT instead...

Answer 2

I have not Oracle here to test, but in general this should work:

SELECT
 Team.team_id,
 Years.year,
 COALESCE(count(Event.event_id),0) events
FROM
 Team
   JOIN (SELECT DISTINCT EXTRACT(YEAR FROM start_date_time) year FROM Event) Years ON 1=1
   LEFT OUTER JOIN Event ON Event.team_id = Team.team_id AND EXTRACT(YEAR FROM Event.start_date_time) = Years.year
GROUP BY
 Years.year,
 team_id
ORDER BY
 year  ASC,
 team_id  ASC
;

You will get results for every year, where you have an event in the events. If that's not enough, you can replace the Years sub with a table filled with all the years.

Answer 3

So it works ! :) Here is my final query :

SELECT
 Team.name,
 Years.year,
 count(Event.event_id)
FROM
    Team
JOIN
    (
        SELECT DISTINCT EXTRACT(YEAR FROM start_date_time) AS year
        FROM Event
    ) Years ON 1 = 1
LEFT OUTER JOIN
    Event ON    Event.team_id = Team.team_id
                AND EXTRACT(YEAR FROM Event.start_date_time) = Years.year
WHERE
    event_type = 'C'
GROUP BY
    Team.name,
    Years.year
ORDER BY
    Year ASC,
    name ASC
;

Thank you !