Board index » delphi » sql union ??

sql union ??

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.

E.g.   SELECT  NAME,SUM(QTY) from TABEL1 GROUP BY NAME
       UNION
       SELECT  NAME2 as NAME, SUM(QTY) from TABEL1 GROUP BY NAME2

 

Re:sql union ??


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.

E.g.   SELECT  NAME,SUM(QTY) from TABEL1 GROUP BY NAME
       UNION
       SELECT  NAME2 as NAME, SUM(QTY) from TABEL1 GROUP BY NAME2

Re:sql union ??


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.

E.g.   SELECT  NAME,SUM(QTY) from TABEL1 GROUP BY NAME
       UNION
       SELECT  NAME2 as NAME, SUM(QTY) from TABEL1 GROUP BY NAME2

Re:sql union ??


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>...

Quote

> 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.

> E.g.   SELECT  NAME,SUM(QTY) from TABEL1 GROUP BY NAME
>        UNION
>        SELECT  NAME2 as NAME, SUM(QTY) from TABEL1 GROUP BY NAME2

Re:sql union ??


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>...

Quote

> 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.

> E.g.   SELECT  NAME,SUM(QTY) from TABEL1 GROUP BY NAME
>        UNION
>        SELECT  NAME2 as NAME, SUM(QTY) from TABEL1 GROUP BY NAME2

Re:sql union ??


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

With D3, Tquery performing on local dbase III table.
I tried with UNION ALL but still get the same result.
I wonder if I can do it without the UNION clause

Other Threads