sql-使用多个where语句将同一列的结果分隔成不同的cloumn

fcg9iug3 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(343)

我以前没有sql知识。我正在尝试为每个folionumber获取不同transactiontype的所有debittransactions的总和
我有下面的代码，但它给出了每个交易类型的所有借方交易的总和。但我想要的是一栏中每一对开本的所有商品及服务税（106）之和，第二栏中每一对开本的所有征税（105）之和，第三栏中每一对开本的所有客房收入（100）之和。

---non working code
SELECT FolioNumber,
       (Select SUM(debitTransactions) FROM HISTTRN WHERE transactionType = 106) as gst,
       (Select SUM(debitTransactions) FROM HISTTRN WHERE transactionType = 105) as levy,
       (Select SUM(debitTransactions) FROM HISTTRN WHERE transactionType = 100) as roomRevenue
FROM
HISTTRN
GROUP BY FolioNumber
ORDER BY FolioNumber

如果我只需要一个事务类型，但需要3个不同的事务类型，那么下面的代码就可以工作了

---working code
SELECT FolioNumber,
       SUM(debitTransactions) as gst 
FROM HISTTRN 
WHERE transactionType = 106

GROUP BY FolioNumber
ORDER BY FolioNumber

有没有可能的办法？谢谢

sql group-by subquery

来源：https://stackoverflow.com/questions/63139938/sql-separate-results-of-same-column-into-different-cloumns-using-multiple-wher

1条答案

按热度按时间

6bc51xsx1#

SELECT FolioNumber,
       SUM(case when transactionType = 106 then debitTransactions else 0 end) as gst,
       SUM(case when transactionType = 105 then debitTransactions else 0 end) as levy,
       SUM(case when transactionType = 100 then debitTransactions else 0 end) as roomRevenue
FROM HISTTRN 
GROUP BY FolioNumber
ORDER BY FolioNumber

赞(0）回复(0）举报 2021-07-29

我来回答

sql-使用多个where语句将同一列的结果分隔成不同的cloumn

1条答案

相关问题

热门标签

最新问答