accumulative summation per row

**omid020** · Apr 8, 2010 06:59

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|
+----+---------------------+-------------+------------+------------------+

**swampBoogie** · Apr 8, 2010 07:27

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

**omid020** · Apr 8, 2010 08:32

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 ?

**swampBoogie** · Apr 8, 2010 08:52

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;

**omid020** · Apr 8, 2010 10:50

Thanks

**omid020** · Apr 9, 2010 07:19

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

**omid020** · May 6, 2010 23:55

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 ?

**guido2004** · May 7, 2010 00:07

Can you give example data of both tables and the final result you want to get?

**omid020** · May 7, 2010 03:41

Originally Posted by guido2004

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 |
+----+---------------------+-------------+----------------+

**omid020** · May 9, 2010 21:14

Has my favorite result uncommon structure ?

Any other idea ?

**guido2004** · May 10, 2010 00:34

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.

**omid020** · May 10, 2010 03:38

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

**omid020** · May 10, 2010 20:41

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 ?

User Tag List

Thread: accumulative summation per row

Thread Tools

Display

accumulative summation per row

Bookmarks

Bookmarks

Posting Permissions