VFP9 SQL Union and Group by

I've seen an example on the web of a VFP9 SQL Union statement with sum() and group by, but can't seem to get it to work here. Is the problem that I'm using SUM in both SELECTS? I can see why that would complicate life, but thought I might get away with it? Both selects work independently, so pretty sure my grammar is right.
test.txt
terrypba1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
The problem is in missing GROUP BY in your SQL statement.

What is your SQLBEHAVIOR setting?
0
terrypba1Author Commented:
Enginebehavior is set to 90.
I do have a Group By clause, and it does work if I separate the Selects and use each with the same Group By clause.
(I hope the text of the Select statement came through?)
0
pcelbaCommented:
LOL, I am blind... but I still see just one GROUP BY.
You'll need GROUP BY clause in both parts of your union. OTOH, possible ORDER BY should be just one.

If you need to sum both "UNIONed" parts first enclose the whole command into another SELECT:
SELECT * FROM ( ;
SELECT ;
		dfrom as ldfrom,dto as ldto,;
		sum(ln.nnet) as monext,sum(ROUND(ln.ncomm_adj*ln.nnet,2)) as moncommdue,;
		sum(ln.nshipped*ph.cost) as moncost,;
	    br.brokername,;
		br.ibrok_id;
	FROM slinvoiceln ln,brokers br,tempslhd hd,prodhistory ph;
	WHERE; 
		br.ibrok_id=ln.ibrok_id1_ln;
		AND ln.iarinv_id=hd.iarinv_id;
		AND BETWEEN(hd.invdate,dfrom,dto);
		AND ph.cglperiod=hd.cglperiod;
		AND ph.iprod_id=ln.iprod_id;
		AND (hd.lsale or hd.lreturn);
		AND ln.nshipped<>0;
	GROUP BY br.ibrok_id, br.brokername, ldfrom, ldto;
	UNION ALL;	
	SELECT ;
		dfrom as ldfrom,dto as ldto,;
		sum(ln.nnet) as monext,sum(ROUND(ln.ncomm_adj2*ln.nnet,2)) as moncommdue,;
		sum(ln.nshipped*ph.cost) as moncost,;
	    br.brokername,;
		br.ibrok_id;
	FROM slinvoiceln ln,brokers br,tempslhd hd,prodhistory ph;
	WHERE; 
		br.ibrok_id=ln.ibrok_id2_ln;
		AND ln.ibrok_id2_ln<>0;
		AND ln.iarinv_id=hd.iarinv_id;
		AND BETWEEN(hd.invdate,dfrom,dto);
		AND ph.cglperiod=hd.cglperiod;
		AND ph.iprod_id=ln.iprod_id;
		AND (hd.lsale or hd.lreturn);
		AND ln.nshipped<>0;
	GROUP BY br.ibrok_id, br.brokername, ldfrom, ldto;
) twoparts ;
GROUP BY ibrok_id, brokername, ldfrom, ldto;
INTO cursor tempmcommsumm 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
BTW, didn't you look for this UNION free command variant?
	SELECT ;
		dfrom as ldfrom,dto as ldto,;
		sum(ln.nnet) as monext,;
		sum(ROUND(IIF(ln.ibrok_id2_ln<>0, ln.ncomm_adj2, ln.ncomm_adj)*ln.nnet,2)) as moncommdue,;
		sum(ln.nshipped*ph.cost) as moncost,;
	    br.brokername,;
		br.ibrok_id;
	FROM slinvoiceln ln,brokers br,tempslhd hd,prodhistory ph;
	WHERE; 
		br.ibrok_id=ln.ibrok_id1_ln;
		AND ln.iarinv_id=hd.iarinv_id;
		AND BETWEEN(hd.invdate,dfrom,dto);
		AND ph.cglperiod=hd.cglperiod;
		AND ph.iprod_id=ln.iprod_id;
		AND (hd.lsale or hd.lreturn);
		AND ln.nshipped<>0;
	GROUP BY br.ibrok_id, br.brokername, ldfrom, ldto ;
	INTO cursor tempmcommsumm 

Open in new window

0
terrypba1Author Commented:
Bingo--thanks! Adding the separate Group By did the trick.
I'll stare at the Union Free suggestion (although I do support unions). The problem is that each line in the invoice can have two different brokers (and commission rates) assigned, so I think the union is needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.