How to club the results of two SQL queries?

Question

SELECT * FROM(
        (SELECT 
            count(DISTINCT RECEPIENT_ID) as NoOfUsers,
            TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT_HSTRY  
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')
        ) b,

        (SELECT 
            count(DISTINCT RECEPIENT_ID) as NoOfUsers, 
            TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT 
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')
        ) f   
        )

this query is returning the data in the following format:

NOOFUSERS              ACCDATE    NOOFUSERS              ACCDATE    
---------------------- ---------- ---------------------- ---------- 
2                      2009-12-21 1                      2010-03-01 
2                      2009-12-21 2                      2010-03-02 
2                      2009-12-21 1                      2010-03-03 
1                      2009-12-23 1                      2010-03-01 

is it possible to club the reuslt of two tables: Am expecting the data to be in this format:

NOOFUSERS              ACCDATE    
---------------------- ---------- 
2                      2009-12-21 
1                      2009-12-23 
1                      2010-01-02 
1                      2010-01-04 //till here its table one data
1                      2010-03-01 //from here its table TWO data
2                      2010-03-02 
1                      2010-03-03 

Thank you :)

Answer 1

Maybe you must use UNION operator? http://dev.mysql.com/doc/refman/5.5/en/union.html

SELECT count(DISTINCT RECEPIENT_ID) as NoOfUsers,
            TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT_HSTRY  
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')

UNION      
    SELECT count(DISTINCT RECEPIENT_ID) as NoOfUsers, 
          TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT 
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')

Answer 2

try the union statements

(select1) union (select2)

    SELECT 
        count(DISTINCT RECEPIENT_ID) as NoOfUsers,
        TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
    FROM 
        ALRT_PLATFORM_ALRT_HSTRY  
    where 
        APPL_CD like 'EBP' and 
        ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
        group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') 

UNION

   SELECT 
        count(DISTINCT RECEPIENT_ID) as NoOfUsers, 
        TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
    FROM 
        ALRT_PLATFORM_ALRT 
    where 
        APPL_CD like 'EBP' and 
        ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
        group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')

Answer 3

You can combine the two results using UNION

Example:

select col1, col2 
from table1 
where...

UNION

select col1, col2 
from table2
where...

Answer 4

Using UNION ALL instead of subselected columns will append subsequent UNION ALL statements to the original select. Be wary you need the same amount of columns in each select statement.

UNION ALL makes sure the combined result is not sorted/mingled (first query is returned before second). A regular UNION can/will mingle/mix/sort the result.

SELECT 
            count(DISTINCT RECEPIENT_ID) as NoOfUsers,
            TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT_HSTRY  
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')


UNION ALL

       SELECT 
            count(DISTINCT RECEPIENT_ID) as NoOfUsers, 
            TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate 
        FROM 
            ALRT_PLATFORM_ALRT 
        where 
            APPL_CD like 'EBP' and 
            ALRT_RSPNS_FROM_CLIENT_ID like 'BB' 
            group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')

Answer 5

As I see, you don't have any join conditions between the two sets (b and f). So use UNION or UNION ALL. Refer here.

Answer 6

If you have a requirement to query these tables together quite often, then you could consider using a view.

create view alrt_platform_alrt_all as
  select * from alrt_platform_alrt
union all
  select * from alrt_platform_alrt_hstry;

This would then allow you to query the data like so:

select 
  count(distinct recepient_id), 
  TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') as accDate
from alrt_platform_alrt_all
where appl_cd like 'EBP'
  and alrt_rspns_from_client_id like 'BB'
group by TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD');