"Merging" results from two queries

[Elvis123]

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

select DATE,CUSTOMER_I D from DB1
select DATE,CUSTOMER_I D 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?

[deepuv04]

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

select DATE,CUSTOMER_I D from DB1
select DATE,CUSTOMER_I D 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_I D from DB1
union all
select DATE,CUSTOMER_I D from DB2

[Elvis123]

Thanks, I really appreciate it!

[Elvis123]

But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(dist inct CUSTOMER_ID) from
(select DATE,CUSTOMER_I D from DB1
union all
select DATE,CUSTOMER_I D from DB2)


but it does not work...

[deepuv04]

But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(dist inct CUSTOMER_ID) from
(select DATE,CUSTOMER_I D from DB1
union all
select DATE,CUSTOMER_I D from DB2)


but it does not work...

you need to group the records based on date

try the following

select T.DATE,count(di stinct T.CUSTOMER_ID) from
(select DATE,CUSTOMER_I D from DB1
union all
select DATE,CUSTOMER_I D from DB2) as T
Group by t.date

if the above not wroks try this


WITH CTE
(
select DATE,CUSTOMER_I D from DB1
union all
select DATE,CUSTOMER_I D from DB2
)SELECT DATE , Count(CUSTOMER_ ID) FROM CTE
GROUP BY DATE

[ck9663]

But how do I do the counting once I have merged the query results? I tried to do it like this:


select DATE,count(dist inct CUSTOMER_ID) from
(select DATE,CUSTOMER_I D from DB1
union all
select DATE,CUSTOMER_I D 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:

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

-- CK

[Elvis123]

Thanks! Now it works!