Using SQL date functions to get totals for last three days
By Rudy Limeback, SQL Consultant, r937.com
SearchOracle.com
I have a table which contains four columns: customer_name, product_name, date sold and quantity. I want to do the sum of quantity groupby product and date sold, and print that in the three days duration, meaning 1st, 2nd, 3rd Jan on 3 Jan, 4,5 and 6 on 6th Jan and so on. How can I do this?
This is actually pretty straightforward.
SELECT product
, datesold
, SUM(quantity) AS total
FROM orders
WHERE datesold IN
( CURRENT_DATE - INTERVAL '2' DAY
, CURRENT_DATE - INTERVAL '1' DAY
, CURRENT_DATE )
GROUP
BY product
, datesold
This assumes, of course, that datesold is a DATE datatype column (and not DATETIME). The exact syntax supported by your particular database system might be slightly different from the above, which is standard SQL. Look in your SQL reference manual under Date Functions.
Oracle White Papers: Fusion Middleware