Sql union returns duplicate results

Question

I have the following query

select count(trade_sid), shortcode from trade where trade.trade_date <= sysdate and trade.trade_date>= add_months(sysdate, -11) group by shortcode UNION ALL select count(trade_sid), shortcode from trade_archive where trade_archive.trade_date <= sysdate and trade_archive.trade_date>= add_months(sysdate, -11) group by shortcode order by shortcode 

This leads to duplication of output like this

 23 abc 24 abc 56 def 87 def 

This is due to the join operator used, any idea how I can modify this query to get

 47 abc 143 def 

as a day off

The user with whom I finally execute this query in java does not have sufficient privileges to create temp tables, is there any other way around this?

Answer 1

Something like that:

 select count(trade_sid), shortcode from ( select trade_sid, shortcode from trade where trade.trade_date <= sysdate and trade.trade_date>= add_months(sysdate, -11) UNION ALL select trade_sid, shortcode from trade_archive where trade_archive.trade_date <= sysdate and trade_archive.trade_date>= add_months(sysdate, -11) ) tt group by shortcode order by shortcode 

Answer 2

You must sum the values ​​and group by short code, for example:

 select sum(count(trade_sid)), shortcode from trade where trade.trade_date <= sysdate and trade.trade_date>= add_months(sysdate, -11) group by shortcode UNION ALL select count(trade_sid), shortcode from trade_archive where trade_archive.trade_date <= sysdate and trade_archive.trade_date>= add_months(sysdate, -11) group by shortcode order by shortcode 

Answer 3

Use UNION , not UNION ALL .

union returns individual records.

The only problem is that your lines do not match.