How to take sum of column with same id in SQL? [duplicate]

Revised question — the TradeID is also needed.

SELECT f.TradeID, f.PricingSecurityID, s.TotalQuantity  FROM FollowingTableStructure AS f  JOIN (SELECT PricingSecurityID, SUM(Quantity) AS TotalQuantity          FROM FollowingTableStructure         GROUP BY PricingSecurityId       ) AS s ON f.PricingSecurityID = s.PricingSecurityID

I'm not wholly convinced the query is sensible, but that's your problem. It can easily be extended to deal with other tables; just add appropriate JOIN clauses.

Please remember to include a table name in the question — it is astonishing how often SQL questions are asked without giving the table a name (so it is not only you who forgets by any means).

Re-updated question

So the originally anonymous table is, apparently, Fireball.dbo.Trade or Fireball..Trade. I'd probably place the 11-way UNION into a view since it is likely to be used in multiple places. However, ignoring that, we can still slip the information into your query:

SELECT t.TradeId,        ISNULL(Securities.SecurityType,'Other') SecurityType,        Securities.TableName,       CASE        WHEN SecurityTrade.SecurityId IS NOT NULL THEN SecurityTrade.SecurityId       ELSE Trade.SecurityId       END AS PricingSecurityID,       s.TotalQuantity AS Quantity,       t.Price,       CASE       WHEN (t.Buy = 1 AND t.Long = 1) THEN 1       WHEN (t.Buy = 0 AND t.Long = 0) THEN 1       ELSE 0       END AS Position  FROM Fireball_Reporting..Trade AS t  JOIN (SELECT PricingSecurityID, SUM(Quantity) AS TotalQuantity          FROM Fireball_Reporting..Trade         GROUP BY PricingSecurityId       ) AS s ON t.PricingSecurityID = s.PricingSecurityID  LEFT JOIN       (SELECT TradeId, 'Bond' SecurityType, 'Fireball.dbo.Bond' TableName FROM Fireball..CorpBondTrade        UNION        SELECT TradeId, 'IRS' SecurityType, 'Fireball.dbo.Bond' TableName FROM Fireball..IRPTrade        UNION        SELECT TradeId, 'Treasury' SecurityType, 'Fireball.dbo.Bond' TableName FROM Fireball..TreasuryTrade        UNION        SELECT TradeId, 'Index' SecurityType, 'Fireball.dbo.CDSIndex' TableName FROM Fireball..CreditIndexTrade        UNION        SELECT TradeId, 'CDS' SecurityType, 'Fireball.dbo.CDS' TableName FROM Fireball..CDSTrade WHERE IsSovereign = 0        UNION        SELECT TradeId, 'Sovereign CDS' SecurityType, 'Fireball.dbo.CDS' TableName FROM Fireball..CDSTrade WHERE IsSovereign = 1        UNION        SELECT TradeId, 'Equity Option' SecurityType, 'Fireball.dbo.EquityOption' TableName FROM Fireball..EquityOptionTrade        UNION        SELECT TradeId, 'Equity' SecurityType, 'Fireball.dbo.Equity' TableName FROM Fireball..EquityTrade        UNION        SELECT TradeId, 'Loan' SecurityType, 'Fireball.dbo.Loan' TableName FROM Fireball..LoanTrade        UNION        SELECT TradeId, 'Swaption' SecurityType, 'Fireball.dbo.Bond' TableName FROM Fireball..SwaptionTrade        UNION        SELECT TradeId, 'Preferred Stock' SecurityType, 'Fireball.dbo.Bond' TableName FROM Fireball..PreferredEquityTrade        --UNION        --SELECT TradeId, 'Bond' SecurityType FROM Fireball..BondTrade       ) AS Securities ON Securities.TradeId = t.TradeId  LEFT JOIN       (SELECT TradeID, SecurityId           FROM Fireball..CDSTrade         UNION        SELECT TradeID, SecurityId          FROM Fireball..CreditIndexTrade                 ) AS SecurityTrade ON SecurityTrade.TradeId = t.TradeId

That's mostly copy and paste — with some reformatting — of your query, with the extra sub-query tucked away in the FROM clause after the Trade table. Were it my query, I'd be using single-letter (or other short mnemonic) aliases for the last two sub-queries too; I just didn't spend the time working out what were appropriate abbreviations for SecurityTrade and Securities.

select PricingSecurityID, sum(Quantity)from tablegroup by PricingSecurityID

select PricingSecurityID, sum(quantity)from Fireball.dbo.Bondgroup by PricingSecurityID