Running Sum Query ?

Following query is calculating running sum and running average of an Invento
ry Item, positive quantities are receipts and negative quantities are issued
, last two columns are running sum of quantity and amount column.
While running sum of quantity is ok, but Amt running sum is not correct, any
idea what am I missing ?
My table data is as under:-
Please note, negative quantites have no rates and no amounts, this will be c
alculated on average basis in above query.
Sno field is just an auto numbering field for query purpose.
PRITC QTY RATE PRAMT SNO
-- -- -- -- --
1111-034 200 1494.5 298900 1
1111-034 218 1559 339862 2
1111-034 -150 3
1111-034 -5 4
1111-034 -200 5
1111-034 -5 6
1111-034 -50 7
Best Regards,
Luqman
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
sum(case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end) Amt
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
SNO QTY RATE AMT SUM(B.QTY) AMT
-- -- -- -- --
--
1 200 1494.5 298900 200 298900
2 218 1559 339862 418 679724
3 -150 1528.13876 -229220.81 268 -687662.44
4 -5 1528.13876 -7640.6938 263 -30562.775
5 -200 1528.13876 -305627.75 63 -1528138.8
6 -5 1528.13876 -7640.6938 58 -45844.163
7 -50 1528.13876 -76406.938 8 -534848.56Hello, Luqman
Please post DDL (as "CREATE TABLE" statements) and sample data (as
"INSERT INTO ... VALUES ..." statements), like this:
CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)
INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
The above DDL is just a guess; please correct it if necessary.
Your current query returns the following error: "Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery." The reason is obvious: you cannot use SUM() for an
expression that contains a subquery (for the last column in your
query).
You probably want something like this:
SELECT a.sno, a.qty, a.rate, a.pramt, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qty_sum, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as avg_rate
FROM test a
If you want something else, please post the expected results.
Razvan|||The following query will output something like this.
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Bal
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 339862.000000
3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
running sum of Amount Field in AmtTotal.
The correct display should be:
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 638762.000000
3 -150.000 1528.138755 -229220.813250 268.000 409541.19
4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
Any idea please ?
Best Regards,
Luqman
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
> Hello, Luqman
> Please post DDL (as "CREATE TABLE" statements) and sample data (as
> "INSERT INTO ... VALUES ..." statements), like this:
> CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> The above DDL is just a guess; please correct it if necessary.
> Your current query returns the following error: "Cannot perform an
> aggregate function on an expression containing an aggregate or a
> subquery." The reason is obvious: you cannot use SUM() for an
> expression that contains a subquery (for the last column in your
> query).
> You probably want something like this:
> SELECT a.sno, a.qty, a.rate, a.pramt, (
> SELECT SUM(qty) FROM test b
> WHERE b.sno<=a.sno
> ) as qty_sum, (
> SELECT SUM(pramt) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as pramt_sum, (
> SELECT SUM(pramt)/SUM(qty) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as avg_rate
> FROM test a
> If you want something else, please post the expected results.
> Razvan
>|||Luqman wrote:
> The correct display should be: [...]
OK, then let's save the SELECT statement in my previous post as a view:
create view myview as
SELECT a.sno, a.qty,
ISNULL(a.rate, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
)) as rate, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qtyTotal, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum
FROM test a
And now we can use the following query:
SELECT x.sno, x.qty, x.rate, x.qty*x.rate as Amount,
x.qtyTotal, x.pramt_sum+isnull((
select sum(qty*rate) from myview y
where y.sno<=x.sno and y.qty<0
),0) as AmtTotal
FROM myview x
The above query returns the expected results (I'm not sure what is the
number of decimals that you want; the number of decimals in the result
changes according to the data types in the table).
However, to be sure that the above query meets all your requirements,
you should also post some sample data where there is a positive value
AFTER some negative values and expected results for this new sample
data.
Razvan|||Hi,
Did you work on my queries, please ?
Best Regards,
Luqman
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:%232Kn49ZiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> The following query will output something like this.
> select a.sno,a.qty,
> case when a.qty>0 then a.rate
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Amt,sum(b.qty),
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Bal
> from test a, test b
> where a.sno>=b.sno
> group by a.sno,a.qty,a.pramt,a.rate
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 339862.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
> 4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
> 5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
> 6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
> 7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
> Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
> running sum of Amount Field in AmtTotal.
> The correct display should be:
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 638762.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 409541.19
> 4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
> 5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
> 6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
> 7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
> Any idea please ?
> Best Regards,
> Luqman
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
>|||Razvan Socol wrote:
Luqman wrote:
> Did you work on my queries, please ?
My last message was:
http://groups.google.com/group/micr...br />
5b133039
Did you read it ?
If yes, you didn't post any additional information. If you need more
help, post sample data AND EXPECTED RESULTS where there is a positive
value after some negative values.
Razvan