title: Oracle : sum this and this and this in a single query
body: <p>I have on order table  containing orders for last week and the ID of the driver who delivered them. It looks a little like this:</p>
  
  <pre><code>ORDERDATE,    ORDERNO,    DRIVER
  23/01/2013,    901398503,    1
  23/01/2013,    901332159,    1
  23/01/2013,    901334158,    2
  24/01/2013,    901338455,    1
  25/01/2013,    902907513,    1
  25/01/2013,    902338553,    2
  25/01/2013,    903936533,    2
  27/01/2013,    903944523,    1
  27/01/2013,    903981522,    2
  27/01/2013,    911334951,    1
  28/01/2013,    911338851,    1
  28/01/2013,    911339259,    1
  28/01/2013,    912332555,    2
  28/01/2013,    912336650,    2
  29/01/2013,    912337655,    1
  29/01/2013,    913969582,    1
  29/01/2013,    913973583,    1
  29/01/2013,    913982552,    1
  29/01/2013,    916379158,    1
  </code></pre>
  
  <p>I'd like to select ORDERDATE, ORDERCOUNT, DRIVER_1_COUNT, DRIVER_2_COUNT. </p>
  
  <p>so, date | total orders  | total orders for driver 1 | total orders for driver 2</p>
  
  <p>Also, I need zeros if ORDERDATE, ORDERCOUNT, DRIVER_1_COUNT or DRIVER_2_COUNT are 0 (or null).</p>
  
  <p>(In oracle) I can select dates for each day last week, and a zero order count (placeholder) for each day like this:</p>
  
  <pre><code>select 
   TRUNC(NEXT_DAY(sysdate,'SUNDAY')-7 +i) ORDERDATE,
   0 as ORDERCOUNT   
  from
   (select rownum i from all_objects where rownum &lt; 8)
  </code></pre>
  
  <p>I should be able to use this output to make sure there are no days missing in the final results (no orders on 26th in this example)</p>
  
  <pre><code>ORDERDATE,ORDERCOUNT
  23/01/2013,0
  24/01/2013,0
  25/01/2013,0
  26/01/2013,0
  27/01/2013,0
  28/01/2013,0
  29/01/2013,0
  </code></pre>
  
  <p>I need this output:</p>
  
  <pre><code>ORDERDATE,ORDERCOUNT,DRIVER_1_COUNT,DRIVER_2_COUNT
  23/01/2013,3,2,1
  24/01/2013,1,1,0
  25/01/2013,3,1,2
  26/01/2013,0,0,0
  27/01/2013,3,2,1
  28/01/2013,4,2,2
  29/01/2013,5,5,0
  </code></pre>
  
  <p>I can get ORDERDATE &amp; ORDERCOUNT(simple sum) and union with the other query to avoid missing days, but I can't work out how to sum for each driver too.</p>
  
  <p>Thanks in advance for you help.</p>
  
  <p>Ed</p>