BorlandTalk.com

MarkR · Guest

Hi guys,

I have a paradox table which I use a select statement using union on to
select two rows per table row.
example statment.

select leftcode as mycode, descript as fits, qtyinstock as stock, qtyonorder
as onorder from mytable union select rightcode as mycode, descript as fits,
qtyinstock2 as stock, qtyonorder2 as onorder from mytable

Now, I really want to return the total on order so i thought if I put
SUM(qtyinstock + qtyonorder) as STOCKAVAIL then this would be great. Alas I
get a message along the lines of you must use GROUP BY when mixing field
types.

So, how can I use GROUP BY in a statement which looks at one table twice for
each entry?

cheers

MarkR · Guest

Please ignore my post. I solved it.

select leftcode as mycode, descript as fits, sum(qtyinstock+ qtyonorder) as
totstock from mytable GROUP BY leftcode union
select leftcode as mycode, descript as fits, sum(qtyinstock2+ qtyonorder2)
as totstock from mytable GROUP BY rightcode

"MarkR" <markcr (AT) wapthespamntlworld (DOT) com> wrote