i working on query report in oracle 10g.
i need generate short list of each course along number of times offered in past year (including ones weren't offered).
i created 1 query
select coursenumber, count(datestart) offered class datestart between (sysdate-365) , sysdate group coursenumber; which produces
coursenumber offered ---- ---------- st03 2 pd01 1 ay03 2 tb01 4 this query correct. ideally want list along coursenumber hy , cs in left column 0 or null offered value. have feeling involves join of sorts, far have tried doesn't produce classes nothing offered.
the table looks
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
i think should work you, doing subquery.
select distinct c.coursenumber, (select count(*) class class.coursenumber = c.coursenumber , datestart between (sysdate-365) , sysdate ) offered class c 
Comments
Post a Comment