How to take the sum of a column with the same id in SQL?

Question

Possible duplicate:
sql query to summarize data

I have the following table structure

TradeId TableName PricingSecurityID Quantity Price 2008 Fireball.dbo.Bond 506 50 100.0000 2009 Fireball.dbo.Bond 506 50 100.2500 2010 Fireball.dbo.Bond 588 50 100.7500 2338 Fireball.dbo.Bond 588 100 102.5000 

Do I need to take the amount of compliance, or we can tell the PricingSecurityID group by PricingSecurityID

as for PricingSecurityID=506 I should get quantity=100

and for PricingSecurityID=588 I should get quantity=150

How can I write this SQL query?

I tried with a simple by statement command but since I also select tradeid, I get an error: The column "TradeId" is not valid in the select list because it is not contained in the aggregate function or in the GROUP BY clause.

Answer 1

Revised issue - TradeID is also required.

 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 am not completely convinced that the request is reasonable, but this is your problem. It can be easily expanded to deal with other tables; just add the appropriate JOIN clauses.

---

Be sure to include the table name in the question - it is amazing how often SQL questions are asked without indicating the table name (so you have not only forgotten any means).

---

Re-updated Question

So, the initially anonymous table is apparently Fireball.dbo.Trade or Fireball..Trade . I would probably put the 11-position UNION in the view, as it is likely to be used in several places. However, ignoring this, we can put the information in your request:

 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 

This is basically copying and pasting - with some reformatting - of your request, with an additional subquery hidden in the FROM clause after the Trade table. If this were my request, I would use single-letter (or other short mnemonic) aliases for the last two subqueries; I just did not waste time developing what was a suitable shortcut for SecurityTrade and Securities.

Answer 2

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

Answer 3

 select PricingSecurityID, sum(Quantity) from table group by PricingSecurityID 

Answer 4

 SELECT PricingSecurityID, SUM(ISNULL(Quantity,0)) FROM Table GROUP BY PricingSecurityId;