468,247 Members | 1,442 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,247 developers. It's quick & easy.

"Merging" results from two queries

I want to "merge" the results of two queries:

select DATE,CUSTOMER_ID from DB1
select DATE,CUSTOMER_ID from DB2

Then I want to be able to count the number of customers for each date in the "merged" result.

What is the best way to solve this?
Jan 2 '08 #1
6 2504
deepuv04
227 Expert 100+
I want to "merge" the results of two queries:

select DATE,CUSTOMER_ID from DB1
select DATE,CUSTOMER_ID from DB2

Then I want to be able to count the number of customers for each date in the "merged" result.

What is the best way to solve this?


select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2
Jan 2 '08 #2
Thanks, I really appreciate it!
Jan 2 '08 #3
But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(distinct CUSTOMER_ID) from
(select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2)


but it does not work...
Jan 2 '08 #4
deepuv04
227 Expert 100+
But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(distinct CUSTOMER_ID) from
(select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2)


but it does not work...
you need to group the records based on date

try the following

select T.DATE,count(distinct T.CUSTOMER_ID) from
(select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2) as T
Group by t.date

if the above not wroks try this


WITH CTE
(
select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2
)SELECT DATE , Count(CUSTOMER_ID) FROM CTE
GROUP BY DATE
Jan 3 '08 #5
ck9663
2,878 Expert 2GB
But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(distinct CUSTOMER_ID) from
(select DATE,CUSTOMER_ID from DB1
union all
select DATE,CUSTOMER_ID from DB2)


but it does not work...
in your sample, you are retrieving record from a subquery. your subquery becomes some sort of a view. so you need to put an alias on the subquery

try:

Expand|Select|Wrap|Line Numbers
  1. select DATE,count(distinct CUSTOMER_ID) from
  2. (select DATE,CUSTOMER_ID from DB1
  3. union all
  4. select DATE,CUSTOMER_ID from DB2) myTableAlias
  5. group by DATE
  6.  
-- CK
Jan 3 '08 #6
Thanks! Now it works!
Jan 3 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by DarkSpy | last post: by
52 posts views Thread by Markus Elfring | last post: by
9 posts views Thread by Gomaw Beoyr | last post: by
4 posts views Thread by John Smith | last post: by
13 posts views Thread by eman1000 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.