Summed up in multiple time for query

[koklimabc]

Dear all,

As per topic that I've some problem in using sum function in my foxpro's query. The sum value suppose be assigned in one times but It is summed-up value in few times based on total number record in grids. The problems of sum function won't be exists in MSSQL query where it is always assigned in one times only. By the way, I'm using VFP6. Can anyone explain what is the problem?

Here is my query in coding

create table tmp1 (paidid c(15),gros_amt n(12,2))
insert into tmp1 values("PA2015001",12.56)
insert into tmp1 values("PA2015001",30.56)

create table tmp2 (paidid c(15),paid n(12,2))
insert into tmp2 values("PA2015001",100.00)

Different on first sections
select sum(t1.gros_amt) as gros_amt, ;
sum(t2.paid) as paid ; =>Assign sum function here.
from tmp1 t1 ;
left outer join tmp2 t2 ;
on alltrim(t1.paidid) == alltrim(t2.paidid) =>Result for Field "Paid" is "200.0".This is incorrect result.
=>If i using on MSSQL query, it will be return "100.0" as I want.

Different on second sections
select sum(t1.gros_amt) as gros_amt,;
t2.paid as paid ; =>Remove sum function here.
from tmp1 t1 ;
left outer join tmp2 t2 ;
on alltrim(t1.paidid) == alltrim(t2.paidid) =>Result for Field "Paid" is "100.0".This is correct result.

Thanks appopriate to someone could helps.

 

[vgulielmus]

In order to understand what's happened, you can imagine that

SELECT ....
from tmp1 t1 ;
left outer join tmp2 t2

creates this temporary table

paidid c(15), gros_amt n(12,2), paidid(2) c(15), paid n(12,2)

with the following values
[pre]"PA2015001" , 12.56 , "PA2015001" , 100.00
"PA2015001" , 30.56 , "PA2015001" , 100.00[/pre]

The solution is to use

min(t2.paid) as paid

or

max(t2.paid) as paid

instead of

sum(t2.paid) as paid

Your query is SET ENGINEBEHAVIOR 70 compliant. The second query will take the last value.
In VFP8 or VFP9 or other RDBMS this query will fail

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[koklimabc]

Thanks to vgulielmus.

 

[Olaf Doschke]

It's not wrong, but not a general solution to sum min or max. You want to sum each t2 amount only once, then you can't join, joining doubles the records, as you have multiple matches.

I wondered, why you said you get 100 from MSSQL, and tried it, the syntax is a bit different, using table variables, but the query is the same:

declare @tmp1 as table (paidid char(15), gros_amt float);
declare @tmp2 as table (paidid char(15), paid float);

insert into @tmp1 values('PA2015001',12.56);
insert into @tmp1 values('PA2015001',30.56);

insert into  @tmp2 values('PA2015001',100.00);

select sum(t1.gros_amt) as gros_amt,
 sum(t2.paid) as paid
 from @tmp1 t1 
 left outer join @tmp2 t2 
 on t1.paidid = t2.paidid;

Results in

[pre]gros_amt paid
43,12 200[/pre]

So MSSQL computes the same amount 200, so MSSSQL makes the same error!?

Well, sorry to say so, but you make the same error. This sum is simply 200, because the left outer join adds the paid amount twice, as it's matched twice, once for each tmp1 record.

A join is not working, as you most probably think, you rather think of putting the tables side by side, so tmp1 and tmp2 record 1 join and tmp1 record 2 is unmatched, as there is no second record in tmp2, but that's not how a left outer join works. It's not like cut&paste of the tables side by side into one excel sheet.

Your logic is ill, neither MSSQL nor VFP nor any SQL dialect will compute 100 from sum(paid) in that join condition, they join paid twice and so you get the double sum.

What do you really need? Sum(gros_mat)-sum(paid) perhaps? Then you need to compute them separate from each single table and finally add or subtract the second from the first sum.
Another, but not typical and not at all recommendable solution would be to join by record number, but that concept is not working.

So I think you rather simply want

Select paidid, sum(gros_amt) from tmp1 Group By 1 into cursor curGrosSum
Select paidid, sum(paid) from tmp2 Group By 1 into cursor curPaidSum

And then join these results with each one row per paidid on the paid id.

If you'd want that in one query, you'd do it

Select curGrosSum.paidid, GrosSum, PaidSum From
(Select paidid, sum(gros_amt) GrosSum from tmp1 Group By 1 ) curGrosSum
Left Outer Join
(Select paidid, sum(paid) PaidSum from tmp2 Group By 1) curPaidSum
On curGrosSum.paidid = curPaidSum.paidid

In MSSQL:

 Select curGrosSum.paidid, GrosSum, PaidSum From
(Select paidid, sum(gros_amt) GrosSum from @tmp1 Group By paidid ) curGrosSum
Left Outer Join
(Select paidid, sum(paid) PaidSum from @tmp2 Group By paidid) curPaidSum
On curGrosSum.paidid = curPaidSum.paidid;

This results in
[pre]paidid GrosSum PaidSum
PA2015001 43,12 100[/pre]

So first sum each group (each paidid), then join the summed results, as you then have one row per paidid in both aggregated intermediate result tables.

And as side note: Because both paidid are c(15) there is no need to alltrim them, unless you store the ids left aligned in one and right alined in the other table.

Bye, Olaf.

 

[koklimabc]

Thanks to Olaf.

 

[mjcmkrsr]

Hi,

In this case I prefer an FULL join in order to know if I have paid an advance

Select NVL(curGrosSum.paidid, curPaidSum.PaidId) PaidID, NVL(GrosSum,0) Grossum, NVL(PaidSum,0) Paidsum, ;
			(NVL(paidsum,0) - NVL(grossum,0)) Balance  ;
	FROM (Select paidid, sum(gros_amt) GrosSum from tmp1 Group By 1 ) curGrosSum ;
	FULL JOIN (Select paidid, sum(paid) PaidSum from tmp2 Group By 1) curPaidSum ;
		On curGrosSum.paidid = curPaidSum.paidid ;
	INTO CURSOR test

hth

MK

 

[Olaf Doschke]

MK, that'll do, when there is payment before order, but when is that the case.

Anyway technically for my MSSQL sample with extended sample data it'öö also give a row for paidid only in temp2:

declare @tmp1 as table (paidid char(15), gros_amt float);
declare @tmp2 as table (paidid char(15), paid float);

insert into @tmp1 values('PA2015001',12.56);
insert into @tmp1 values('PA2015001',30.56);
insert into @tmp1 values('PA2015003',60.12);

insert into  @tmp2 values('PA2015001',100.00);
insert into  @tmp2 values('PA2015002',100.00);

Select ISNULL(curGrosSum.paidid, curPaidSum.PaidId) PaidID, ISNULL(GrosSum,0) Grossum, ISNULL(PaidSum,0) Paidsum, 
			(ISNULL(paidsum,0) - ISNULL(grossum,0)) Balance  
	FROM (Select paidid, sum(gros_amt) GrosSum from @tmp1 Group By paidid ) curGrosSum 
	FULL JOIN (Select paidid, sum(paid) PaidSum from @tmp2 Group By paidid ) curPaidSum 
		On curGrosSum.paidid = curPaidSum.paidid