Union Clause
This is a discussion on Union Clause within the Oracle Miscellaneous forums, part of the Oracle Database category; --> Hi, This is my query: SELECT SUM(zcom) + SUM(ze) + SUM(zp) FROM (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom UNION (SELECT ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-07-2008, 11:16 AM
| ||||
| ||||
 Union Clause Hi, This is my query: SELECT SUM(zcom) + SUM(ze) + SUM(zp) FROM (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom UNION (SELECT count(DISTINCT(email)) FROM customers_lookup) ze UNION (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); Basically I want to get a count from the three tables where the email address appears in ALL THREE tables. If an email does not exist in all three tables, then I do not want it counted....... I think I'm barking up the wrong tree with the query above...... John.      |
|
05-07-2008, 11:16 AM
| |||
| |||
| Re: Union Clause On May 5, 9:17 am, Mtek <m...@mtekusa.com> wrote: > Hi, > > This is my query: > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > FROM > (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > UNION > (SELECT count(DISTINCT(email)) FROM customers_lookup) ze > UNION > (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); > > Basically I want to get a count from the three tables where the email > address appears in ALL THREE tables. If an email does not exist in > all three tables, then I do not want it counted....... > > I think I'm barking up the wrong tree with the query above...... > > John. But you show only one table in this query, customers_lookup. Here's a hint in the form of a question: What is the difference between the union operation and the join operation? hth, ed |
|
05-07-2008, 11:16 AM
| |||
| |||
| Re: Union Clause On May 5, 8:46 am, Ed Prochak <edproc...@gmail.com> wrote: > On May 5, 9:17 am, Mtek <m...@mtekusa.com> wrote: > > > > > Hi, > > > This is my query: > > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > > FROM > > (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > > UNION > > (SELECT count(DISTINCT(email)) FROM customers_lookup) ze > > UNION > > (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); > > > Basically I want to get a count from the three tables where the email > > address appears in ALL THREE tables. If an email does not exist in > > all three tables, then I do not want it counted....... > > > I think I'm barking up the wrong tree with the query above...... > > > John. > > But you show only one table in this query, customers_lookup. > > Here's a hint in the form of a question: > What is the difference between the union operation and the join > operation? > > hth, > ed Well, I figured that UNION removed duplicates. So, I was trying to get a count of the email addresses that exist in all three tables. I made a mistake in my query: SELECT SUM(zcom) + SUM(ze) + SUM(zp) FROM (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom UNION (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze UNION (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp); There are the three tables. So, I only want to include emails which are in all three tables for my counts.... Thanks for the reply. John |
|
05-07-2008, 11:16 AM
| |||
| |||
| Re: Union Clause On May 5, 8:56*am, Mtek <m...@mtekusa.com> wrote: > On May 5, 8:46 am, Ed Prochak <edproc...@gmail.com> wrote: > > > > > > > On May 5, 9:17 am, Mtek <m...@mtekusa.com> wrote: > > > > Hi, > > > > This is my query: > > > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > > > FROM > > > * * (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > > > * * UNION > > > * * (SELECT count(DISTINCT(email)) FROM customers_lookup) ze > > > * * UNION > > > * * (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); > > > > Basically I want to get a count from the three tables where the email > > > address appears in ALL THREE tables. *If an email does not exist in > > > all three tables, then I do not want it counted....... > > > > I think I'm barking up the wrong tree with the query above...... > > > > John. > > > But you show only one table in this query, customers_lookup. > > > Here's a hint in the form of a question: > > What is the difference between *the union operation and the join > > operation? > > > hth, > > * ed > > Well, I figured that UNION removed duplicates. *So, I was trying to > get a count of the email addresses that exist in all three tables. *I > made a mistake in my query: > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > FROM > * * (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > * * UNION > * * (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze > * * UNION > * * (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp); > > There are the three tables. > > So, I only want to include emails which are in all three tables for my > counts.... > > Thanks for the reply. > > John- Hide quoted text - > > - Show quoted text - And this invocation of UNION won't provide the results you seek as you generate a COUNT before you can eliminate the duplicates. Setting up an example: SQL> create table customers_lookup ( 2 email varchar2(128), 3 customer varchar2(40) 4 ); Table created. SQL> create table customers_lookup_ze as select * From customers_lookup; Table created. SQL> create table customers_lookup_prim as select * From customers_lookup; Table created. SQL> insert all 2 into customers_lookup 3 values('myemail', null) 4 into customers_lookup 5 values('youremail', null) 6 into customers_lookup 7 values('theiremail', null) 8 select * from dual; 3 rows created. SQL> commit; Commit complete. SQL> insert into customers_lookup_ze 2 select * From customers_lookup 3 where email not like 'your%' 4 / 2 rows created. SQL> commit; Commit complete. SQL> insert into customers_lookup_prim 2 select * from customers_lookup 3 where email not like 'their%' 4 / 2 rows created. SQL> commit 2 / Commit complete. One possibility of a working query, using UNION ALL, might be: SELECT email FROM (SELECT email FROM customers_lookup UNION ALL SELECT email FROM customers_lookup_ze UNION ALL SELECT email FROM customers_lookup_prim) group by email having count(*) = 3; -------------------------------------------------------------------------------- myemail Of course you have other options: select l.email from customers_lookup l join customers_lookup_ze z on (z.email = l.email) join customers_lookup_prim p on (p.email = z.email); -------------------------------------------------------------------------------- myemail Or: select email from customers_lookup where email in (select email from customers_lookup_ze where email in (select email from customers_lookup_prim)); -------------------------------------------------------------------------------- myemail But, your current query won't produce anything: SQL> SELECT SUM(zcom) + SUM(ze) + SUM(zp) 2 FROM 3 (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom 4 UNION 5 (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze 6 UNION 7 (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp); (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze * ERROR at line 5: ORA-00933: SQL command not properly ended The closest example to your query which actually works is: SELECT SUM(l.zcom) + SUM(z.ze) + SUM(p.zp) FROM (SELECT count(DISTINCT(email)) zcom FROM customers_lookup) l, (SELECT count(DISTINCT(email)) ze FROM customers_lookup_ze) z, (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) p; and it produces the following result from my data: SUM(L.ZCOM)+SUM(Z.ZE)+SUM(P.ZP) ------------------------------- 7 which isn't usable in my estimation as it provides nothing in the way of information regarding how the data satisfies your criteria (same email in all three tables). Look at the three working examples I've provided and work from that point forward. David Fitzjarrell |
|
05-07-2008, 11:16 AM
| |||
| |||
| Re: Union Clause On May 5, 9:58 am, "fitzjarr...@cox.net" <orat...@msn.com> wrote: > On May 5, 8:56 am, Mtek <m...@mtekusa.com> wrote: > > > > > On May 5, 8:46 am, Ed Prochak <edproc...@gmail.com> wrote: > > > > On May 5, 9:17 am, Mtek <m...@mtekusa.com> wrote: > > > > > Hi, > > > > > This is my query: > > > > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > > > > FROM > > > > (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > > > > UNION > > > > (SELECT count(DISTINCT(email)) FROM customers_lookup) ze > > > > UNION > > > > (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); > > > > > Basically I want to get a count from the three tables where the email > > > > address appears in ALL THREE tables. If an email does not exist in > > > > all three tables, then I do not want it counted....... > > > > > I think I'm barking up the wrong tree with the query above...... > > > > > John. > > > > But you show only one table in this query, customers_lookup. > > > > Here's a hint in the form of a question: > > > What is the difference between the union operation and the join > > > operation? > > > > hth, > > > ed > > > Well, I figured that UNION removed duplicates. So, I was trying to > > get a count of the email addresses that exist in all three tables. I > > made a mistake in my query: > > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > > FROM > > (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > > UNION > > (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze > > UNION > > (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp); > > > There are the three tables. > > > So, I only want to include emails which are in all three tables for my > > counts.... > > > Thanks for the reply. > > > John- Hide quoted text - > > > - Show quoted text - > > And this invocation of UNION won't provide the results you seek as > you generate a COUNT before you can eliminate the duplicates. Setting > up an example: > > SQL> create table customers_lookup ( > 2 email varchar2(128), > 3 customer varchar2(40) > 4 ); > > Table created. > > SQL> create table customers_lookup_ze as select * From > customers_lookup; > > Table created. > > SQL> create table customers_lookup_prim as select * From > customers_lookup; > > Table created. > > SQL> insert all > 2 into customers_lookup > 3 values('myemail', null) > 4 into customers_lookup > 5 values('youremail', null) > 6 into customers_lookup > 7 values('theiremail', null) > 8 select * from dual; > > 3 rows created. > > SQL> commit; > > Commit complete. > > SQL> insert into customers_lookup_ze > 2 select * From customers_lookup > 3 where email not like 'your%' > 4 / > > 2 rows created. > > SQL> commit; > > Commit complete. > > SQL> insert into customers_lookup_prim > 2 select * from customers_lookup > 3 where email not like 'their%' > 4 / > > 2 rows created. > > SQL> commit > 2 / > > Commit complete. > > One possibility of a working query, using UNION ALL, might be: > > SELECT email > FROM > (SELECT email FROM customers_lookup > UNION ALL > SELECT email FROM customers_lookup_ze > UNION ALL > SELECT email FROM customers_lookup_prim) > group by email > having count(*) = 3; > > -------------------------------------------------------------------------------- > myemail > > Of course you have other options: > > select l.email > from customers_lookup l join customers_lookup_ze z on (z.email = > l.email) join customers_lookup_prim p on (p.email = z.email); > > -------------------------------------------------------------------------------- > myemail > > Or: > > select email > from customers_lookup > where email in (select email from customers_lookup_ze > where email in (select email from > customers_lookup_prim)); > > -------------------------------------------------------------------------------- > myemail > > But, your current query won't produce anything: > > SQL> SELECT SUM(zcom) + SUM(ze) + SUM(zp) > 2 FROM > 3 (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > 4 UNION > 5 (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze > 6 UNION > 7 (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) > zp); > (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze > * > ERROR at line 5: > ORA-00933: SQL command not properly ended > > The closest example to your query which actually works is: > > SELECT SUM(l.zcom) + SUM(z.ze) + SUM(p.zp) > FROM > (SELECT count(DISTINCT(email)) zcom FROM customers_lookup) l, > (SELECT count(DISTINCT(email)) ze FROM customers_lookup_ze) z, > (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) p; > > and it produces the following result from my data: > > SUM(L.ZCOM)+SUM(Z.ZE)+SUM(P.ZP) > ------------------------------- > 7 > which isn't usable in my estimation as it provides nothing in the way > of information regarding how the data satisfies your criteria (same > email in all three tables). > > Look at the three working examples I've provided and work from that > point forward. > > David Fitzjarrell Thanks David, I'm sure one of those will work for me....I'll give them a try. |
|
05-07-2008, 11:16 AM
| ||||
| ||||
| Re: Union Clause Mtek wrote: > Hi, > > This is my query: > > SELECT SUM(zcom) + SUM(ze) + SUM(zp) > FROM > (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom > UNION > (SELECT count(DISTINCT(email)) FROM customers_lookup) ze > UNION > (SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp); > > Basically I want to get a count from the three tables where the email > address appears in ALL THREE tables. If an email does not exist in > all three tables, then I do not want it counted....... Use INTERSECT, not UNION. Or use a normal inner join... select count(distinct a.email) from customers_lookup a inner join customers_lookup_ze b on b.email = a.email inner join customers_lookup_prim c on c.email = b.email -- Peter |
Linear Mode
