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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading