Sql code when using in Oracle database

Question

While going through some of your data, you see that there are some outliers in shipdate that have badly affected your results. Use the following as the basis for your query but strip off all shipdates less than 0 or greater than 365 days difference from the order date.

Here is the SQL I have written up so far. What am I missing?

SELECT ol.shipdate - o.orderdate as day, COUNT(*) as numol
FROM dw.orders o JOIN dw.orderline ol ON o.orderid = ol.orderid
group by (ol.shipdate - o.orderdate)
ORDER BY 1;

This problem is out of a book that I'm working on by myself.

you haven't specified a WHERE criteria

logixologist
– logixologist

2014-10-23 21:00:58 +00:00
Commented Oct 23, 2014 at 21:00 — logixologist
– logixologist, Commented Oct 23, 2014 at 21:00

Allan · Accepted Answer · 2014-10-23 21:17:17Z

2

You should be using a WHERE clause to filter the results. For example:

SELECT ol.shipdate - o.orderdate as day, COUNT(*) as numol
FROM dw.orders o JOIN dw.orderline ol ON o.orderid = ol.orderid
WHERE (ol.shipdate - o.orderdate) BETWEEN 0 AND 365
GROUP BY (ol.shipdate - o.orderdate)
ORDER BY 1;

However, instead of repeating (ol.shipdate - o.orderdate) many times, you may opt to put it in a subquery like this:

SELECT day, COUNT(*)
FROM
(
    SELECT (ol.shipdate - o.orderdate) AS day
    FROM dw.orders o
    JOIN dw.orderline ol
    ON o.orderid = ol.orderid
)
WHERE day BETWEEN 0 AND 365
GROUP BY day
ORDER BY day;

Note: here's the documentation on the BETWEEN condition. It's the same as - in our case - doing something like (day >= 0 AND day <= 365).

edited Oct 23, 2014 at 21:17

Allan

17.5k5 gold badges54 silver badges73 bronze badges

answered Oct 23, 2014 at 20:59

Mr. Llama

21k3 gold badges70 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mike Over a year ago

that where clause doesn't work in the sql command in oracle @Mr.Llama

Allan Over a year ago

@mike: The where clause is just located in the wrong place in this answer. It should be before the GROUP BY.

mike Over a year ago

@Allan I had WHERE (ol.shipdate - o.orderdate) BETWEEN 0 AND 365 on top of the GROUP BY and still didn't work says it not properly ended

ErstwhileIII · Accepted Answer · 2014-10-23 21:04:12Z

1

You may want to consider how you simply translate the english into a "where" clause

 Where (ol.shipdate - o.orderdate) > 0 
   and (ol.shipdate - o.orderdate) < 366

answered Oct 23, 2014 at 21:04

ErstwhileIII

4,8722 gold badges25 silver badges37 bronze badges

Collectives™ on Stack Overflow

Sql code when using in Oracle database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related