SQL use sum without grouping by primary keys

Pratik 0
Viewed 34

I am trying out a query wherein I want only one single sum for two different rows.

select     sum(holdings) 
from       market_data.mstar_holdings mh
inner join market_data.holder_type ht 
           on ht.holder_type_code = mh.mstar_security_specific_holder_type
where      mstar_investment_id = 'E0USA00AY9' 
           and ht.adjustment_flag = true 
           and ht.is_consolidated =false 
--group by ownership_percentage,
           ht.adjustment_threshold 
having ownership_percentage>ht.adjustment_threshold

Currently, in output, I am getting two records. 1000089 and 616295556 in two rows. But I want the output as 1000089+616295556 in one single row. If I comment the group by line, it is giving me error. Is there any way I'll get only one record each time summing up all holdings rows?

sql
1 Answers

The query you placed in the comments is different from the one posted in the question. At any rate, this works:

select sum(holdings)
from (
    select     sum(holdings) as holdings
    from       market_data.mstar_holdings mh
    inner join market_data.holder_type ht 
               on ht.holder_type_code = mh.mstar_security_specific_holder_type
    where      mstar_investment_id = 'E0USA00AY9' 
               and ht.adjustment_flag = true 
               and ht.is_consolidated =false 
    group by ownership_percentage, ht.adjustment_threshold 
    --having ownership_percentage >ht.adjustment_threshold --explain why you have this here. May need to move elsewhere, such as where statement. 
)z

Fiddle found here.

Isolated0
Related