This is sample table content :
+----+---------------------+-------------+------------+
| 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 :
+----+---------------------+-------------+------------+------------------
| 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|
+----+---------------------+-------------+------------+------------------+
select id,
date,
productName,
saleAmount,
(select sum(saleAmount)
from productTable as q
where q.productName = productTable.productName
and q.date <= productTable.date)
from productTable
tnx , but final result in third row isn`t correctly !
I used this query as your guidance :
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 :
+----+---------------------+-------------+------------+------------------+
| 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 | [COLOR="Red"][B]35[/B] [/COLOR]|
| 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 ?
My fault, as you have multiple records for the same date, all those will get included in the sum.
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;
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 :
+----+---------------------+-------------+----------------+
| 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 :
SELECT f.date,
f.productName,
([COLOR="YellowGreen"]HERE WHAT COULD I WRITE
TO CALCULATE ACCUMULATIVE
VALUE OF (SUM OF PURCHASES
- SUM OF SALES) TILL AVAILABLE
DATE IN EACH ROW[/COLOR]) AS [COLOR="Red"]INVENTORY[/COLOR],
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
Im really dummy with it ! I tried below query but got this error : [I]Table 'dbName.f' doesnt exit .[/I]
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 ? 
Can you give example data of both tables and the final result you want to get?
First table is purchase (or inputs) :
+----+---------------------+-------------+----------------+
|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) :
+----+---------------------+-------------+------------+
|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 :
+----+---------------------+-------------+----------------+
| 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 |
+----+---------------------+-------------+----------------+
Has my favorite result uncommon structure ? 
Any other idea ?
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:
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.
Mmmm , but I need to do it with sql :o
Just immagine having to modify it in the future
After 4 weeks , which future ??? 
I thought it should be easier than it flashes 
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 ?
