如何在SQL中获取具有相同ID的列的总和? [英] How to take sum of column with same id in SQL?
问题描述
可能的重复:
对数据求和的sql查询
我有以下表格结构
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
我需要取Quantity
匹配的总和,或者我们可以说按特定PricingSecurityID
I need to take a sum of Quantity
of matching or we can say group by particular PricingSecurityID
喜欢 PricingSecurityID=506
我应该得到 quantity=100
like for PricingSecurityID=506
I should get quantity=100
对于 PricingSecurityID=588
我应该得到 quantity=150
and for PricingSecurityID=588
I should get quantity=150
如何编写此 SQL 查询?
How can I write this SQL query?
我确实尝试过使用简单的 group by 语句但由于我也在选择 tradeid,因此出现错误:选择列表中的列TradeId"无效,因为它既不包含在聚合函数中,也不包含在 GROUP BY 子句中.
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.
推荐答案
修正的问题 — 还需要 TradeID.
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
我并不完全相信这个查询是明智的,但那是你的问题.可以很容易地扩展以处理其他表;只需添加适当的 JOIN 子句即可.
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.
请记住在问题中包含一个表名——令人惊讶的是,SQL 问题被问到而不给表一个名称的频率令人惊讶(因此,无论如何,不仅仅是您会忘记).
重新更新问题
所以最初的匿名表显然是Fireball.dbo.Trade
或Fireball..Trade
.我可能会将 11-way UNION 放入视图中,因为它可能会在多个地方使用.但是,忽略这一点,我们仍然可以将信息插入到您的查询中:
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
这主要是对您的查询进行复制和粘贴 - 并进行了一些重新格式化,额外的子查询隐藏在 Trade 表之后的 FROM 子句中.如果是我的查询,我也会在最后两个子查询中使用单字母(或其他短助记符)别名;我只是没有花时间研究 SecurityTrade 和 Securities 的适当缩写.
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.
这篇关于如何在SQL中获取具有相同ID的列的总和?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!