Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1

    Answered: query to find a daily difference

    Hi,
    I m a new SQL leaner, and got my self into a difficult question,
    let's say i have this table:
    HTML Code:
    create table purchase(
        datePurchase date  
        itemiId int,
        nbItemSold int
        cstmrId int
    );
    How can I get the daily difference of the product sold ?

    Ex:
    on the 2017-02-01 sold 4 product1
    on the 2017-02-01 sold 3 product2
    on the 2017-02-01 sold 1 product3
    on the 2017-02-02 sold 1 product1
    on the 2017-02-02 sold 2 product2
    on the 2017-02-02 sold 1 product3
    on the 2017-02-03 sold 5 product2
    on the 2017-02-03 sold 1 product3
    on the 2017-02-03 sold 0 product1

    Query result would be (getting the difference between the number of the product sold on the D day with those sold on the D-1 day):
    2017-02-02 product1 -3
    2017-02-02 product2 -1
    2017-02-02 product3 1
    2017-02-03 product1 -1
    2017-02-03 product2 3
    2017-02-03 product3 0

    the customer doesn't matter. i just want to find the daily difference in selling products
    Last edited by bozmin; 03-17-17 at 22:59.

  2. Best Answer
    Posted by bozmin

    "thank you for your help, that s exactly what i wanted"


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    This can be done using a combination of grouping (to get the daily total) and window functions

    Code:
    select datepurchase, itemiid, daily_total, 
           daily_total - lag(daily_total) over (partition by itemiid order by datepurchase) as diff
    from (
      select datepurchase, 
             itemiid, 
             sum(nbitemsold) as daily_total
      from purchase
      group by datepurchase, itemiid
    ) t
    order by datepurchase, itemiid
    Online example: http://rextester.com/BNH69199
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Feb 2017
    Posts
    5
    Provided Answers: 1

    Thumbs up query works

    thank you for your help, that s exactly what i wanted

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •