SEO

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.

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

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

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;

EMAIL
--------------------------------------------------------------------------------
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);

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));

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

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;
>
> EMAIL
> --------------------------------------------------------------------------------
> 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);
>
> 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));
>
> EMAIL
> --------------------------------------------------------------------------------
> 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.

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