sql union returns duplicate results

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 reults in duplicate output like this

23 abc
24 abc 
56 def
87 def

This is because of the union operator which is used, any idea how I can change this query so th开发者_开发问答at i get

47 abc
143 def  

as the output

The user with which i finally execute this query in java does not have sufficient privileges to create temp tables is there any other way of going about this?

Something like this:

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

You must sum the values and group by the shortcode, like this:

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

Use UNION, not UNION ALL.

union returns distinct records.

The only problem is that your rows are not the same.