By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,846 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

"Merging" results from two queries

P: 4
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
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
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

P: 4
Thanks, I really appreciate it!
Jan 2 '08 #3

P: 4
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
Expert 100+
P: 227
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
Expert 2.5K+
P: 2,878
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

P: 4
Thanks! Now it works!
Jan 3 '08 #7

Post your reply

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