CODEWITHSUNDEEP
sqloracle-database
mike
mike

Reputation: 1

Sql code when using in Oracle database

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.

Upvotes: 0

Views: 40

Answers (2)

Mr. Llama
Mr. Llama

Reputation: 20889

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).

Upvotes: 2

ErstwhileIII
ErstwhileIII

Reputation: 4843

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

Upvotes: 1

Related Questions