Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

sql experts around?



  • to be precise: SqLite is what I need. My head is steaming - I did not manage to solve my problem.

    Consider this:
    There is a table that describes purchases:

    int productId
    date dateOfPurchase
    float value
    

    Now I want a SQL statement, that gives me a new table like this:

    dateOfPurchase  | productId | sum_of_purchase_last_year(value)
    

    And with last year I mean the time between dateOfPurchase and one year before that date. My problem is, that the window changes with every line of the table, so I started doubting, if this is at all possible.
    I tried with the sqlite window functions (OVER and RANGE) but ... I failed.

    Looking forward for your ideas!
    HM



  • @kshegunov Heureka!
    You put me onto the right track: I found the following SQL working on SQLITE, solving my problem:

    SELECT 
      productId 
    , dateOfP 
    , (SELECT sum(value) 
       FROM (SELECT value 
             FROM purchases AS pu 
             WHERE pu.productId = purchases.productId 
                   AND pu.dateOfP > DATE(purchases.dateOfP, '-1 years') 
                   AND pu.dateOfP <= purchases.dateOfP) ) AS Summe
    
    FROM purchases
    GROUP BY dateOfP
    

    So thank you and also to Christian, who pointed into the right direction!



  • Download "DB Browser for Sqlite"
    Build tables and look at creation strings.


  • Moderators

    @HoMa said in sql experts around?:

    Now I want a SQL statement, that gives me a new table like this:

    I suppose you mean a query that produces such a recordset?
    Something like this:

    SELECT productId, dateOfPurchase, SUM(value) WHERE dateOfPurchase > date('now', '-1 years') GROUP BY (productId)
    

    Obviously I haven't tested it, but if my old and frail memory serves it should work.



  • Hi and thank you for this reply. This is - unfortunately - not what I am looking for. The period here is always the same: from one year ago until now.
    But I want to have it so that it is always 1 year before dateOfPurchase until dateOfPurchase. Having the time slot differently for each row in the result table is, what makes it so difficult - I think.
    ;) Maybe there are other ideas in your old and frail memories?

    Best regards
    HM


  • Lifetime Qt Champion

    Then replace

    date('now', '-1 years')

    with a subselect which queries this value.


  • Moderators

    @HoMa said in sql experts around?:

    But I want to have it so that it is always 1 year before dateOfPurchase until dateOfPurchase.

    This truly makes no sense to me, but you could refer to the dateOfPurchase column in the where clause instead of 'now'.



  • Thx - again.
    This is - unfortunately - again not working. I played around and used a "GROUP BY dateOfPurchase" - which got me closer. I now have one row for each date - which is what I want - and I get a SUM - but it is not what one would expect. Actually I do not understand the values, that get displayed.

    This is the latest, but not working "prototype" SQL:

    SELECT DATE(dateOfPurchase, '-1 years') AS from, dateOfPurchase AS to, productId, SUM(value) /100.
    FROM purchases
    WHERE dateOfPurchase > DATE(dateOfPurchase, '-1 years') AND dateOfPurchase <= bis
    GROUP BY dateOfPurchase
    


  • @HoMa

    WHERE dateOfPurchase > DATE(dateOfPurchase, '-1 years') AND dateOfPurchase <= bis

    I don't know what bis is, but don't you think dateOfPurchase > DATE(dateOfPurchase, '-1 years') is always true?
    @kshegunov remarked on this in your description of what you say you want.


  • Moderators

    I believe something like this is what is intended (in the spirit of what @Christian-Ehrlicher said):

    SELECT productId as refProduct, dateOfPurchase as refDate, SUM( SELECT value FROM purchases WHERE dateOfPurchase > date(refDate, '-1 years') AND productId = refProduct) AS totalValue  FROM purchases
    

    This is one heavy query, though.



  • Yes - quite impressive. But it looks very plausible. I get a "no such column" on refDate though ...



  • @kshegunov Heureka!
    You put me onto the right track: I found the following SQL working on SQLITE, solving my problem:

    SELECT 
      productId 
    , dateOfP 
    , (SELECT sum(value) 
       FROM (SELECT value 
             FROM purchases AS pu 
             WHERE pu.productId = purchases.productId 
                   AND pu.dateOfP > DATE(purchases.dateOfP, '-1 years') 
                   AND pu.dateOfP <= purchases.dateOfP) ) AS Summe
    
    FROM purchases
    GROUP BY dateOfP
    

    So thank you and also to Christian, who pointed into the right direction!


Log in to reply