SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question accumulative summation per row

    This is sample table content :
    Code:
    +----+---------------------+-------------+------------+
    | ID | date                | productName | saleAmount |
    +----+---------------------+-------------+------------+
    |  1 | 2010-04-06 00:00:00 | screw       |         20 |
    |  2 | 2010-04-07 00:00:00 | nut         |         10 |
    |  3 | 2010-04-07 00:00:00 | screw       |          5 |
    |  4 | 2010-04-07 00:00:00 | screw       |         10 |
    |  5 | 2010-04-08 00:00:00 | nut         |         30 |
    |  6 | 2010-04-08 00:00:00 | screw       |          5 |
    +----+---------------------+-------------+------------+
    Is it possible to make a query that return all available dates (rows) per product and
    return accumulative value for sale amount only till date in same row ? My desired result
    for above table could be like below :

    Code:
    +----+---------------------+-------------+------------+------------------
    | ID | date                | productName | saleAmount | accumulativeSale |
    +----+---------------------+-------------+------------+------------------
    |  1 | 2010-04-06 00:00:00 | screw       |         20 |                20|
    |  2 | 2010-04-07 00:00:00 | nut         |         10 |                10|
    |  3 | 2010-04-07 00:00:00 | screw       |          5 |                25|
    |  4 | 2010-04-07 00:00:00 | screw       |         10 |                35|
    |  5 | 2010-04-08 00:00:00 | nut         |         30 |                40|
    |  6 | 2010-04-08 00:00:00 | screw       |          5 |                40|
    +----+---------------------+-------------+------------+------------------+

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    699
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select id,
           date,
           productName,
           saleAmount,
           (select sum(saleAmount)
              from productTable as q
             where q.productName = productTable.productName
               and q.date <= productTable.date)
      from productTable

  3. #3
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tnx , but final result in third row isn`t correctly !
    I used this query as your guidance :

    Code MySQL:
    select id,
           date,
           productName,
           saleAmount,
           (select sum(saleAmount)
              from sold as q
             where q.productName = sold.productName
               and q.date <= sold.date) As accumulativeSale
      from sold;

    And the result :
    Code:
    +----+---------------------+-------------+------------+------------------+
    | id | date                | productName | saleAmount | accumulativeSale |
    +----+---------------------+-------------+------------+------------------+
    |  1 | 2010-04-06 00:00:00 | screw       |         20 |               20 |
    |  2 | 2010-04-07 00:00:00 | nut         |         10 |               10 |
    |  3 | 2010-04-07 00:00:00 | screw       |          5 |               35 |
    |  4 | 2010-04-07 00:00:00 | screw       |         10 |               35 |
    |  5 | 2010-04-08 00:00:00 | nut         |         30 |               40 |
    |  6 | 2010-04-08 00:00:00 | screw       |          5 |               40 |
    +----+---------------------+-------------+------------+------------------+
    35 value should be 25 ; Am I wrong with something ?

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    699
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    My fault, as you have multiple records for the same date, all those will get included in the sum.

    Code:
    SELECT id,
           DATE,
           productName,
           saleAmount,
           (SELECT sum(saleAmount)
              FROM sold as q
             WHERE q.productName = sold.productName
               AND q.id <= sold.id) As accumulativeSale
      FROM sold;

  5. #5
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

  6. #6
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to extend above example , now there is a new table that I
    name it (purchase) and finally I need to calculate difference
    of sum of purchase amount and sum of sale amount till every available
    date in those 2 tables . Also I want to see all purchases and sales
    in final result . This is purchase table content :
    Code:
    +----+---------------------+-------------+----------------+
    | ID | date                | productName | purchaseAmount |
    +----+---------------------+-------------+----------------+
    |  1 | 2010-04-03 16:42:56 | screw       |             20 |
    |  2 | 2010-04-03 16:43:43 | nut         |             10 |
    |  3 | 2010-04-03 16:44:44 | screw       |             35 |
    |  4 | 2010-04-04 16:45:53 | nut         |             40 |
    |  5 | 2010-04-07 16:46:29 | screw       |             15 |
    |  6 | 2010-04-08 16:47:52 | screw       |              5 |
    |  7 | 2010-04-08 16:50:37 | nut         |              5 |
    +----+---------------------+-------------+----------------+
    I tried to write a query with below structure but I`m confused for
    writing upper query :
    Code:
    SELECT f.date,
           f.productName,
           (HERE WHAT COULD I WRITE
            TO CALCULATE ACCUMULATIVE
            VALUE OF (SUM OF PURCHASES
            - SUM OF SALES) TILL AVAILABLE
            DATE IN EACH ROW) AS INVENTORY,
    
    FROM(
    SELECT date,
           productName,
           Null AS saleAmount,
           purchaseAmount,
           (SELECT sum(purchaseAmount)
              FROM purchase AS q
             WHERE q.productName = purchase.productName
               AND q.ID <= purchase.ID) AS accumulativePurchase
      FROM purchase
    UNION ALL
    SELECT date,
           productName,
           saleAmount,
           Null AS purchaseAmount,
           (SELECT SUM(saleAmount)
              FROM sold AS q
             WHERE q.productName = sold.productName
               AND q.id <= sold.id) AS accumulativeSale
      FROM sold
    ) AS f
    Thanks in advance

  7. #7
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I`m really dummy with it ! I tried below query but got this error :
    Table 'dbName.f' doesn`t exit .

    Code:
    SELECT f.date,
           f.productName,
           (SELECT (SUM(purchaseAmount)
            - SUM(saleAmount))
           FROM f AS z
           WHERE z.productName = purchase.productName
            AND  z.productName = sold.productName
            AND  z.ID <= purchase.ID
            AND  z.ID <= sold.id ) AS INVENTORY
    
    
    FROM(
    ---here is subquery---
    ) AS f
    How its logic should be ?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Can you give example data of both tables and the final result you want to get?

  9. #9
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Can you give example data of both tables and the final result you want to get?
    First table is purchase (or inputs) :
    Code:
    +----+---------------------+-------------+----------------+
    |inID| date                | productName | purchaseAmount |
    +----+---------------------+-------------+----------------+
    |  1 | 2010-04-03 00:00:00 | screw       |             20 |
    |  2 | 2010-04-03 00:00:00 | nut         |             10 |
    |  3 | 2010-04-03 00:00:00 | screw       |             35 |
    |  4 | 2010-04-04 00:00:00 | nut         |             40 |
    |  5 | 2010-04-07 00:00:00 | screw       |             15 |
    |  6 | 2010-04-08 00:00:00 | screw       |              5 |
    |  7 | 2010-04-08 00:00:00 | nut         |              5 |
    +----+---------------------+-------------+----------------+
    And second table is sold (or outputs) :
    Code:
    +----+---------------------+-------------+------------+
    |outID| date                | productName | saleAmount |
    +----+---------------------+-------------+------------+
    |  1 | 2010-04-06 00:00:00 | screw       |         20 |
    |  2 | 2010-04-07 00:00:00 | nut         |         10 |
    |  3 | 2010-04-07 00:00:00 | screw       |          5 |
    |  4 | 2010-04-07 00:00:00 | screw       |         10 |
    |  5 | 2010-04-08 00:00:00 | nut         |         30 |
    |  6 | 2010-04-08 00:00:00 | screw       |          5 |
    +----+---------------------+-------------+------------+
    Final result should show the difference of inputs and outputs for all available dates but
    with cumulative values :
    Code:
    +----+---------------------+-------------+----------------+
    | ID | date                | productName |currentInventory|
    +----+---------------------+-------------+----------------+
    |  1 | 2010-04-03 00:00:00 | screw       |             20 |
    |  2 | 2010-04-03 00:00:00 | nut         |             10 |
    |  3 | 2010-04-03 00:00:00 | screw       |             55 |
    |  4 | 2010-04-04 00:00:00 | nut         |             50 |
    |  1 | 2010-04-06 00:00:00 | screw       |             35 |
    |  5 | 2010-04-07 00:00:00 | screw       |             50 |
    |  2 | 2010-04-07 00:00:00 | nut         |             40 |
    |  3 | 2010-04-07 00:00:00 | screw       |             45 |
    |  4 | 2010-04-07 00:00:00 | screw       |             35 |
    |  6 | 2010-04-08 00:00:00 | screw       |             40 |
    |  7 | 2010-04-08 00:00:00 | nut         |             45 |
    |  5 | 2010-04-08 00:00:00 | nut         |             15 |
    |  6 | 2010-04-08 00:00:00 | screw       |             35 |
    +----+---------------------+-------------+----------------+

  10. #10
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Has my favorite result uncommon structure ?
    Any other idea ?

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    IMO the query to do all this (if possible) would become too complex. Just immagine having to modify it in the future

    Why don't you do something like this:
    Code MySQL:
    SELECT 
        0 AS tableorder
      , id
      , DATE
      , productName
      , purchaseAmount AS amount
    FROM purchase
    UNION
    SELECT 
        1
      , id
      , DATE
      , productName
      , -saleAmount
    FROM purchase
    ORDER BY 
        DATE
      , tableorder
      , id
    And then just loop through the resultset in PHP (or whatever language you use) and create the output you need.

  12. #12
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mmmm , but I need to do it with sql

    Just immagine having to modify it in the future
    After 4 weeks , which future ???
    I thought it should be easier than it flashes

  13. #13
    SitePoint Enthusiast omid020's Avatar
    Join Date
    Feb 2008
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible to create two views for purchase and sold tables while every view shows the cumulative values till each date and finally create another view to calculate the subtract of purchases and sales for each date via two other views ?


Bookmarks

Posting Permissions

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