SQL query self join

I am working on a query for a report in Oracle 10g.

I need to generate a short list of each course along with the number of times they were offered in the past year (including ones that weren't actually offered).

I created one query

SELECT coursenumber, count(datestart) AS Offered
FROM class
WHERE datestart BETWEEN (sysdate-365) AND sysdate
GROUP BY coursenumber;

Which produces

COURSENUMBER    OFFERED
----           ----------
ST03              2
PD01              1
AY03              2
TB01              4

This query is all co开发者_如何学Crrect. However ideally I want it to list those along with COURSENUMBER HY and CS in the left column as well with 0 or null as the OFFERED value. I have a feeling this involves a join of sorts, but so far what I have tried doesn't produce the classes with nothing offered.

The table normally looks like

REFERENCE_NO DATESTART TIME TIME        EID     ROOMID COURSENUMBER
------------ --------- ---- ---- ---------- ---------- ----
         256 03-MAR-11 0930 1100          2          2 PD01
         257 03-MAY-11 0930 1100         12          7 PD01
         258 18-MAY-11 1230 0100         12          7 PD01
         259 24-OCT-11 1930 2015          6          2 CS01
         260 17-JUN-11 1130 1300          6          4 CS01
         261 25-MAY-11 1900 2000         13          6 HY01
         262 25-MAY-11 1900 2000         13          6 HY01
         263 04-APR-11 0930 1100         13          5 ST03
         264 13-SEP-11 1930 2100          6          4 ST03
         265 05-NOV-11 1930 2100          6          5 ST03
         266 04-FEB-11 1430 1600          6          5 ST03
         267 02-JAN-11 0630 0700         13          1 TB01
         268 01-FEB-11 0630 0700         13          1 TB01
         269 01-MAR-11 0630 0700         13          1 TB01
         270 01-APR-11 0630 0700         13          1 TB01
         271 01-MAY-11 0630 0700         13          1 TB01
         272 14-MAR-11 0830 0915          4          3 AY03
         273 19-APR-11 0930 1015          4          3 AY03
         274 17-JUN-11 0830 0915         14          3 AY03
         275 14-AUG-09 0930 1015         14          3 AY03
         276 03-MAY-09 0830 0915         14          3 AY03

---

SELECT
  coursenumber,
  COUNT(CASE WHEN datestart BETWEEN (sysdate-365) AND sysdate THEN 1 END) AS Offered
FROM class
GROUP BY coursenumber;

So, as you can see, this particular problem doesn't need a join.

---

I think something like this should work for you, by just doing it as a subquery.

SELECT distinct c.coursenumber, 
       (SELECT COUNT(*)
        FROM class
        WHERE class.coursenumber = c.coursenumber
          AND datestart BETWEEN (sysdate-365) AND sysdate
        ) AS Offered
FROM class c

---

I like jschoen's answer better for this particular case (when you want one and only one row and column out of the subquery for each row of the main query), but just to demonstrate another way to do it:

select t1.coursenumber, nvl(t2.cnt,0)
from class t1 left outer join (
 select coursenumber, count(*) cnt
 from class
 where datestart between (sysdate-365) AND sysdate
 group by coursenumber
) t2 on t1.coursenumber = t2.coursenumber