I join two SELECT * ...   command to perform a query, but the UIONed
result give me duplicate numbers.  what should I do to make them add
together?

Many thanks.

I join two SELECT * ...   command to perform a query, but the UIONed
result give me duplicate numbers.  what should I do to make them add
together?

Many thanks.

I join two SELECT * ...   command to perform a query, but the UIONed
result give me duplicate numbers.  what should I do to make them add
together?

Many thanks.

As long as each value for 'name' occurs in both columns NAME and NAME2 on
seperate records then you could try something like

SELECT name, SUM(a.QTY+b.QTY)
from tabel1 as a, tabel2 as b
where a.name = b.name2;

If not then you might want to start looking at outer joins and/or building
a temporary table using your original select and then summing over that.

Hope this helps and isn't too far wide of the mark - I don't use local SQL,
my exspertise is in Oracle.

Alan

aelhk <ae...@aelhk.com> wrote in article
<MPG.f68d78c20962ee989...@news.hk.super.net>...

In Oracle, the UNION construct will remove any duplicate records.  This if
course slows down the query a little because there is an implied ordering
in removing the duplicates.

If a similar record is retrieved from each select and you want to see it
twice then you would have to use the UNION ALL constract.

Are yoiu sure that there is not some difference in the record you are
gettign back twice?  If not then the flavour (yes I'm a brit) of SQL you
are using acts differently from Oracle (which is the only SQL I'm familiar
with).  You might try something like

(Oracle Syntax)
SELECT a.name, SUM(a.qty+b.qty)
FROM table1 a, table1 b
where a.name = b.name2

but this will only work if as long as each name has entries in columns NAME
and NAME2 somewhere and not in the same record.

Donlt knwo if all this helps but there yoyu go...

Alan

aelhk <ae...@aelhk.com> wrote in article
<MPG.f68d783e876a19989...@news.hk.super.net>...

I'm really lost here.
My table consts of entry with name placing order for up to 3 items with a
qty.

John   Apple  nil  nil  3
Paul   Orange Apple nil  1

I want to sum up the items ordered.
Your example don;t seem to return what I want with 'A.Name=B.Name' since
I don't expect them to equal.
So I do a UNION like

SELECT ITEM1,SUM(QTY) FROM TABLE1 where (item1 is not null) GROUP BY
ITEM1
UNION
SELECT ITEM2,SUM(QTY) FROM TABLE1 where (item2 is not null) GROUP BY
ITEM2

I get :

Apple 3
Apple 1
Orange 1