Using SQL date functions to get totals for last three days

Using SQL date functions to get totals for last three days

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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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.

This was first published in January 2009

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top