CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
BIReportGuy
BIReportGuy

Reputation: 817

SQL Summed amount from two unions into one row

How do I sum the amount from two queries using union all into one column and one row? Right now it's broken down into two rows, but I only want the difference on the TotalQty column to be in a single row.

    select sum(begsdquantity)as 'TotalQty', CUSIPNumber, ForeignCountry
from 
    (
        select sum (abs(b.begsdquantity)) begsdquantity, B.CUSIPNumber, C.FOREIGNCOUNTRY 
        from USBI.vw_NameAddressBase a
        inner join USBI.vw_StockRecord b on a.FirmAccountId = b.FirmAccountId
        inner join USBI.vw_SecurityBase c on b.FirmCUSIPId = c.FirmCUSIPId
        WHERE '20160229' between a.EffectiveDate and a.ExpirationDate
        AND '20160229' between b.EffectiveDate and b.ExpirationDate
        and '20160229' between c.EffectiveDate and c.ExpirationDate
        AND c.ForeignCountry NOT IN ('US', 'VA', 'RQ', 'VQ')
        and b.CUSIPNumber = '654902204'
        and b.AcctType in ('0') 
        and b.LocMemo = 's'
        GROUP BY B.CUSIPNumber, C.ForeignCountry        

        union ALL

        select sum(-b.begsdquantity) begsdquantity, B.CUSIPNumber, C.ForeignCountry 
        from USBI.vw_NameAddressBase a
        INNER join USBI.vw_StockRecord b on a.FirmAccountId = b.FirmAccountId
        inner join USBI.vw_SecurityBase c on b.FirmCUSIPId = c.FirmCUSIPId
        where a.RestrDate in ('99999999')
        AND a.CloseRestrictInd in ('C')
        AND c.ForeignCountry NOT IN ('US', 'VA', 'RQ', 'VQ')
        and '20160229' between a.EffectiveDate and a.ExpirationDate
        and '20160229' between b.EffectiveDate and b.ExpirationDate
        and '20160229' between c.EffectiveDate and c.ExpirationDate
        and b.CUSIPNumber = '654902204'
        GROUP BY B.CUSIPNumber, C.ForeignCountry

    ) t

group by begsdquantity, CUSIPNumber, ForeignCountry

ORDER BY CUSIPNumber

My Results:

enter image description here

I'm looking for the difference of the TotalQty column of 96061 in one column and one row. Is this possible or what am I doing wrong?

Upvotes: 0

Views: 50

Answers (1)

Sql Surfer
Sql Surfer

Reputation: 1422

Make the final line ...

group by CUSIPNumber, ForeignCountry

i.e take out the begsdquantity in the group by

Upvotes: 3

Related Questions