CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Talha
Talha

Reputation: 12717

How to merge two result

I have two type of sql query it returns same data, i need to merge two of them, if i use Union it puts second result to first result but i dont need this, i need merge date or are there a way to merge two sql query to one query ?

SELECT TO_CHAR(s.CR_DATE, 'DD') DAY, TO_CHAR(s.CR_DATE, 'MM') MONTH, count(*) total FROM SUSR s
left outer join SUSR_ACCT sa on (s.SUSR_ID = sa.SUSR_ID) 
WHERE s.CR_DATE > '01-01-2012' AND s.CR_DATE < '01-01-2014' and sa.ACCT_ID =  123
GROUP BY TO_CHAR(s.CR_DATE, 'DD'), TO_CHAR(s.CR_DATE, 'MM') 



SELECT TO_CHAR(s.CR_DATE, 'DD') DAY, TO_CHAR(s.CR_DATE, 'MM') MONTH, count(*) total FROM SUSR s
WHERE s.CR_DATE > '01-01-2012' AND s.CR_DATE < '01-01-2014' and s.APP_ACCT_ID =  123
GROUP BY TO_CHAR(s.CR_DATE, 'DD'), TO_CHAR(s.CR_DATE, 'MM')
ORDER BY month, day

Thanks !

enter image description here

Upvotes: 0

Views: 111

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726709

Assuming that I understand the intention behind your merging strategy correctly, you should add a second level of GROUP BY, and total up the corresponding totals, like this:

SELECT DAY, MONTH, SUM(total) FROM (
    SELECT TO_CHAR(s.CR_DATE, 'DD') DAY, TO_CHAR(s.CR_DATE, 'MM') MONTH, count(*) total
    FROM SUSR s
    left outer join SUSR_ACCT sa on (s.SUSR_ID = sa.SUSR_ID) 
    WHERE s.CR_DATE > '01-01-2012' AND s.CR_DATE < '01-01-2014' and sa.ACCT_ID =  123
    GROUP BY TO_CHAR(s.CR_DATE, 'DD'), TO_CHAR(s.CR_DATE, 'MM') 
UNION ALL
    SELECT TO_CHAR(s.CR_DATE, 'DD') DAY, TO_CHAR(s.CR_DATE, 'MM') MONTH, count(*) total
    FROM SUSR s
    WHERE s.CR_DATE > '01-01-2012' AND s.CR_DATE < '01-01-2014' and s.APP_ACCT_ID =  123
    GROUP BY TO_CHAR(s.CR_DATE, 'DD'), TO_CHAR(s.CR_DATE, 'MM') 
) src
GROUP BY DAY, MONTH
ORDER BY DAY, MONTH

If you have a 1 2 5 row in the first result and a 1 2 4 row in the second, you will see a 1 2 9 row in the output (i.e. the first two columns will be used for grouping, and the last one will be totalled up).

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

You want to aggregate the two results:

SELECT day, month, SUM(total)
  FROM ( /* first_query */
        UNION ALL
         /* second_query */)
 GROUP BY day, month

Upvotes: 3

Related Questions