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

oracle

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?

Best Answer

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)
);
Related Question