Maybe this is old news to you all, but it's new to me.
I'm doing some work for a client, and they often want the sum of items
in their (for example) invoice file, PLUS the sum of the same items in
the historical invoice problems.
Obviously a UNION can query both files, but you end up with 2 rows per
item, which you then have to somehow combine.
Got sick of doing it, and Googled around. Here is the actual working
sample from my code, followed by a generic summary. Works in VFP9, I
doubt it would work in 7:
************ CODE SNIPPIT ** suggest cut and paste in text editor to get
rid of wraps
*************************** Wierd TEST ******************* IT
WORKS!!!!!!!!!!!!!!!*********************************
SELECT SUM(nOrder) as nOrder, SUM(nlyOrder) as nlyOrder, SUM(npyOrder)
as npyorder, SUM(noldOrder) as noldorder, cWarehouse, cVendNo ;
FROM (;
Select Sum(IIF(Between(dinvoice, dStart, dEnd),
aritrs.nOrdQty, 0)) as nOrder, ;
Sum(IIF(Between(dinvoice, dLYStart, dLYEnd),
aritrs.nOrdQty, 0)) as nLYOrder, ;
Sum(IIF(Between(dinvoice, dPYStart, dPYEnd),
aritrs.nOrdQty, 0)) as nPYOrder, ;
Sum(IIF(dinvoice <dpYStart, aritrs.nOrdQty, 0)) as
nOldOrder, ;
aritrs.cWarehouse, icitem.cvendno ;
FROM aritrs LEFT OUTER JOIN icitem ON
aritrs.citemno = icitem.citemno;
LEFT OUTER JOIN arinvc ON
aritrs.cinvno = arinvc.cinvno;
where "FRAME" $ Upper(icitem.cClass) ;
GROUP BY cvendno, aritrs.cwarehouse ;
UNION;
Select Sum(IIF(Between(dinvoice, dStart, dEnd),
aritrsh.nOrdQty, 0)) as nOrder, ;
Sum(IIF(Between(dinvoice, dLYStart, dLYEnd),
aritrsh.nOrdQty, 0)) as nLYOrder, ;
Sum(IIF(Between(dinvoice, dPYStart, dPYEnd),
aritrsh.nOrdQty, 0)) as nPYOrder, ;
Sum(IIF(dinvoice <dpYStart, aritrsh.nOrdQty, 0)) as
nOldOrder, ;
aritrsh.cWarehouse, icitem.cvendno ;
FROM aritrsh LEFT OUTER JOIN icitem ON
aritrsh.citemno = icitem.citemno;
LEFT OUTER JOIN arinvch ON
aritrsh.cinvno = arinvch.cinvno;
where "FRAME" $ Upper(icitem.cClass) ;
GROUP BY cvendno, aritrsh.cwarehouse ;
) curtemp GROUP BY cWarehouse, cVendNo INTO CURSOR vendorder ORDER
BY cVendNo
*************************** TEST *****SUCCESSFUL! Now New Code.
***********************************************
Summary:
To SUM both 1/2 of a union:
SELECT SUM(field1) as field1, sum(field2) as field2..... FROM (;
Select Sum(afield) as field1, sum(bfield) as field2.... from myfileOne ;
group by Valid_group_clause;
Union:
Select Sum(afield) as field1, sum(bfield) as field2.... from myfileTwo ;
group by Valid_group_clause ;
) <name_for_internal_results_to_be_stored> GROUP BY
valid_grouping_clause into cursor CoolBeans
Hope this is useful, and not old hat!
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
