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

Question

Possible Duplicate:
sql query to sum the data

I have 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

I need to take a sum of Quantity of matching or we can say group by particular PricingSecurityID

like 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 did try with simple group by statement but as i'm also selecting tradeid i'm getting error : Column 'TradeId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It's a rudimentary aggregate `SUM(Quantity) GROUP BY PricingSecurityID`. Do you need other columns along with it, or just the Quantity? — Michael Berkowski, Oct 12 '12 at 17:47
Three equal answers. I'd suggest to delete the second and third one, for the sake of a cleaner stackoverflow. It's up to the posters though. — daniloquio, Oct 12 '12 at 17:52
It is not clear what is the expected result. For example which value of `tradeId` should be along with `PricingSecurityID=506` ? — Grisha Weintraub, Oct 12 '12 at 18:05
@ashuthinks You are wrong. You say for PricingSecurityId=506 the sum should be 100, but then what TradeId should you see with this 100? 2008 or 2009? Please elaborate more showing us the full result set you expect from, say, PricingSecurityID=506. — daniloquio, Oct 12 '12 at 18:06

Jonathan Leffler · Accepted Answer · 2012-10-12T19:25:30.413

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.

score 12 · Answer 2 · answered Oct 12 '12 at 17:48

12

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

answered Oct 12 '12 at 17:48

Grisha Weintraub

7,803
1
25
45

Thank you! This is nice and short! – Russo Mar 02 '21 at 08:50

score 11 · Answer 3 · answered Oct 12 '12 at 17:49

11

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

answered Oct 12 '12 at 17:49

Johanna Larsson

10,531
6
39
50

score 8 · Answer 4 · answered Oct 12 '12 at 17:56

8

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

answered Oct 12 '12 at 17:56

Kumar_2002

584
1
5
14

`ISNULL` isn't necessary here; any values that are null won't be included, and the `SUM` will be the same. – LittleBobbyTables - Au Revoir Oct 12 '12 at 18:01
updated question not that much simple friends plz help\ thanks – Neo Oct 12 '12 at 18:04
@LittleBobbyTables: It's common to have `ISNULL(SUM(Quantity),0)` though in case there are only nulls in the column (for a group). – ypercubeᵀᴹ Oct 13 '12 at 18:48

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

4 Answers4

Linked