How to take sum of column with same id in SQL?
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.
how to sum multiple rows with same id in SQL Server
Simple GROUP BY
and SUM
should work.
SELECT ID, NAME, SUM([NO])
FROM Your_TableName
GROUP BY ID, NAME;
How to take sum of column with same id and different table in SQL?
You can join
two tables and use sum
aggregate function with the group by
clause to calculate total decCreditLimit
for each szCustId
and bAllowToCredit
:
select t2.szCustId
, t2.bAllowToCredit
, sum(t1.decCreditLimit) as decCreditLimit
from table1 t1
join table2 t2 on t1.szCustId = t2.szCustId
group by t2.szCustId
, t2.bAllowToCredit
sql query to sum of same id but different value?
You can just do like:
select buyer_id, sum(prod_barter_val) from add_to_cart group by buyer_id
Related Topics
Many to Many Relation Design - Intersection Table Design
How to Import a SQL Data File into SQL Server
Query to List SQL Server Stored Procedures Along with Lines of Code for Each Procedure
How to Create a Closure Table Using Data from an Adjacency List
Are There Any Free Tools to Generate 'Insert Into' Scripts in Ms SQL Server
Trigger to Prevent Insertion for Duplicate Data of Two Columns
SQL Update Statement to Switch Two Values in Two Rows
Insert into Table Without Specifying Column Names
Delete Command Is Too Slow in a Table with Clustered Index
Pl/SQL Performance Tuning for Like '%...%' Wildcard Queries
SQL Server: Delete All the Rows of All the Tables
Oracle Pivot Query Gives Columns with Quotes Around the Column Names. What
Rails Virtual Attribute Search or SQL Combined Column Search
Why Isn't Set Xact_Abort on the Default Behavior
Gather_Plan_Statistics Does Does Not Generate Basic Plan Statistics
SQL Server - Does [Select] Lock [Update]