Oracle: Helping hand writing this query. Do I need a sub query here?

Question

I have the below query that returns the number of orders created on a particular customer number per month for the past 2 years.

SQL

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1
             ELSE 0
           END) AS val_2013,
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2014 THEN 1
             ELSE 0
           END) AS val_2014
FROM   orders o
WHERE  o.customer_no = '0'
       AND EXTRACT(year FROM entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM') 

Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01        201        205
02        246        235
03        286        243
04        234        206
05        251        161
06        217        187
07        239        210
08        190        204
09        206        224
10        266        175
11        171          0
12        133          0

12 rows selected.

Question

The query above returns the number of orders but I also want the value of these orders in the same format as above.

I have a table order_totals which has order_final_total but there is no customer number column in this table. This is the part that is confusing me. Instead of the SUM of the number of orders, I need the SUM of the order value but I don't understand how to get this for just the orders on a particular customer number.

Expected Results

TO   VAL_2013   VAL_2014
-- ---------- ----------
01     9201.42    3467.67
02     1287.94    7819.25
...

Do I need to use a sub query, to first get the order numbers?

I've only been using Oracle for around 2 months so any help is appreciated!

50% there query

Ok I've come up with the below query that shows the value of these orders over the past 2 years split in to months but in a single column. I couldn't work out how to split it by year as the above query. Any ideas?

SELECT TO_CHAR(o.entry_date, 'MM'),
       SUM(t.order_final_tot)
FROM   orders o,
       order_totals t
WHERE  o.order_no = t.order_no
       AND o.customer_no = '0'
       AND EXTRACT(year FROM o.entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM')

Results

TO SUM(T.ORDER_FINAL_TOT)
-- ----------------------
01              222333.09
02              216587.53
03              257628.37
04              245838.47
05               227823.5
06               241722.9
07              270359.57
08              277249.43
09              245064.45
10              227929.83
11               85159.13
12               56982.31

12 rows selected.

@ypercube's answer

I get:

MO ORDER_TOTAL_2013 ORDER_TOTAL_2014
-- ---------------- ----------------
03                0            88.26
05                0             45.2
06             16.1            23.75
07                0            69.37
09                0           116.68

Why do I only get months 03, 05, 06, 07 and 09?

Answer 1

Using PIVOT:

WITH
   combined AS (
      SELECT
         o.customer_no,
         EXTRACT(month FROM o.entry_date) AS order_month, 
         EXTRACT(year FROM o.entry_date) AS order_year, 
         t.order_final_tot
      FROM orders o
      JOIN order_totals t ON (o.order_no = t.order_no)
   )
SELECT *
FROM combined
PIVOT (
   SUM(order_final_tot) AS ord_tot, 
   COUNT(*) AS ord_cnt
   FOR order_year IN (2013, 2014)
);

Answer 2

If (and only if) order_no is the primary (or a unique) key of order_totals, then you could use a similar CASE expression as in your first query, to get the order values:

SELECT 
       TO_CHAR(o.entry_date, 'MM') AS month,

       -- count orders
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1
             ELSE 0
           END) AS val_2013,
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2014 THEN 1
             ELSE 0
           END) AS val_2014,

       -- order totals
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2013 THEN t.order_final_tot
             ELSE 0
           END) AS order_total_2013,
       SUM(CASE
             WHEN EXTRACT(year FROM entry_date) = 2014 THEN t.order_final_tot
             ELSE 0
           END) AS order_total_2014
FROM   orders o
  JOIN order_totals t
    ON o.order_no = t.order_no
WHERE  o.customer_no = '0'
  AND  EXTRACT(year FROM o.entry_date) IN ( 2014, 2013 )
GROUP  BY TO_CHAR(o.entry_date, 'MM') ;

---

Additional points:

• Instead of:

  WHERE EXTRACT(year FROM o.entry_date) IN ( 2014, 2013 )
  

  you could use:

  WHERE o.entry_date >= DATE '2013-01-01' 
    AND o.entry_date < DATE '2015-01-01'
  

  and similarly for the conditions inside the CASE expressions. This might be more efficient, if there are indexes on the date column. For this particular query, it will not make much difference (as one customer will have few rows) but in a query that involves all customers or in general a larger part or the whole table, it will make a difference.