Geek Answers Handbook

SQL query support

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

I need to create a short list of each course, as well as the number of times they offered last year (including those that were not actually offered).

I created one request

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

What produces

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

This request is all correct. However, ideally, I want him to list them along with COURSENUMBER HY and CS in the left column, as well as 0 or null as the SUGGESTED value. I have a feeling that this is connected with the collection, but so far what I tried does not give classes in which nothing was offered.

The table usually 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
+4
source share
3 answers

I think something like this should work for you, 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
+1
source 
 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 does not require pooling.

+2
source

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

 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
+1
source

More articles:


All Articles