2

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?

  • 1
    What are the columns of the orders table? Is there a order_value column? It would help if you added the CREATE TABLE orders statement. +1 for the clearly explained question. – ypercubeᵀᴹ Oct 17 '14 at 8:30
  • @ypercube Thanks for the feedback. I just realized (old query) I seem to be counting the number of times that entry date is seen, rather than the number of orders with that entry date. Does it matter? It's the same result. To answer your question, the only thing that links orders and order_totals is orders.order_no and order_totals.order_no. There are no totals in orders. Does that help? – user3973427 Oct 17 '14 at 8:33
  • Then perhaps you have an order_details table that has that info (the price of an order item)? – ypercubeᵀᴹ Oct 17 '14 at 8:34
  • @ypercube Thanks but that's what I'm stuck on. I can count the number of orders as per the existing query but now I need to count the value (pulling in information from order_totals) and that's what I'm stuck on. – user3973427 Oct 17 '14 at 8:36
  • Add the CREATE TABLE statements then, of both tables. Do both tables have order_no as primary key? – ypercubeᵀᴹ Oct 17 '14 at 8:46
2

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)
);
0

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.

  • Thanks for the answer. The JOIN seems to be causing a problem. ORA-00933: SQL command not properly ended just after the JOIN. – user3973427 Oct 17 '14 at 12:39
  • There was a comma missing, after AS val_2014 – ypercubeᵀᴹ Oct 17 '14 at 12:41
  • I added that missing comma already. – user3973427 Oct 17 '14 at 12:42
  • What is your Oracle version? – ypercubeᵀᴹ Oct 17 '14 at 12:42
  • 8. Do I have to use WHERE? – user3973427 Oct 17 '14 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.