UNION and GROUP BY

Maybe it's just Friday but can I have a couple of SELECTs joined with UNION
and then GROUP BY on the final output?

* in this example, I'm wanting a single record, ref='A', qty=15

CREATE CURSOR a1 (ref C(1), qty N(2,0))
CREATE CURSOR b1 (ref C(1), qty N(2,0))

INSERT INTO a1 (ref, qty) ;
VALUES ('A', 10)
INSERT INTO b1 (ref, qty) ;
VALUES ('A', 5)

SELECT ref, qty ;
FROM a1 ;
UNION ;
SELECT ref, qty ;
FROM b1 ;
GROUP BY 1

--
TIA
Andrew Howell

man-wai
Fri Aug 26 09:38:35 CDT 2005

you should have an agregated column for the "GROUP BY"...

> SELECT ref, qty ;
> FROM a1 ;
> UNION ;
> SELECT ref, qty ;
> FROM b1 ;
> GROUP BY 1
>


--
.~. Might, Courage, Vision. http://www.linux-sxs.org
/ v \
/( _ )\ Linux 2.4.31
^ ^ 10:37pm up 3 days 2:14 load average: 0.83 0.97 0.86

Anders
Fri Aug 26 14:11:27 CDT 2005

You have to do it in to steps.
SELECT ref, qty ;
FROM a1 ;
UNION ;
SELECT ref, qty ;
FROM b1 ;
INTO CURSOR Q1
SELECT ref , MAX(qty) AS qty ;
FROM Q1 GROUP BY ref

If you only want ref='A' you can add that to eacg query in step 1 or in step
2.
IN VFP9 you can roll this into one
SELECT ref, MAX(qty) FROM ;
(SELECT ref, qty from A1 ;
UNION SELECT ref, qty FROM B1) AS Q1 ;
GROUP BY ref

-Anders


Den 05-08-26 12.22, i artikeln u#xykoiqFHA.908@tk2msftngp13.phx.gbl, skrev
"Andrew Howell" <ajh@work>:

> Maybe it's just Friday but can I have a couple of SELECTs joined with UNION
> and then GROUP BY on the final output?
>
> * in this example, I'm wanting a single record, ref='A', qty=15
>
> CREATE CURSOR a1 (ref C(1), qty N(2,0))
> CREATE CURSOR b1 (ref C(1), qty N(2,0))
>
> INSERT INTO a1 (ref, qty) ;
> VALUES ('A', 10)
> INSERT INTO b1 (ref, qty) ;
> VALUES ('A', 5)
>
> SELECT ref, qty ;
> FROM a1 ;
> UNION ;
> SELECT ref, qty ;
> FROM b1 ;
> GROUP BY 1