c# - 如何在两个条件的单个查询中获取多个 SUM()

Question

我在 MS Access 中有表:

Id | name  | code  |        ttime           | total | type  -----------------------------------------------------------1  | Abc   | 123   | 10-Feb-18 4:04:48 PM   | 2.01  | RS2  | Abd   | 122   | 11-Feb-18 4:04:48 PM   | 3.90  | RS3  | Abe   | 125   | 12-Feb-18 4:04:48 PM   | 23.00 | WS //other type4  | Abf   | 124   | 13-Feb-18 4:04:48 PM   | 2.11  | RS5  | Abg   | 126   | 13-Feb-18 5:04:48 PM   | 8.01  | WS // here too6  | Abh   | 127   | 14-Feb-18 4:04:48 PM   | 5.01  | RS7  | Abi   | 128   | 15-Feb-18 4:04:48 PM   | 9.10  | RS

I need to take sum of total only RS type in first column and sum of total of WS type in second column as:

SELECT SUM(total) AS rstotal, 
       SUM(total) AS wstotal, 
       COUNT(code) 
WHERE ttime > '09-Feb-18 4:04:48 PM'

什么条件或 self 加入会有所帮助，如何帮助？结果应该是这样的:

rstotal | wstotal | count-------------------------22.13   | 31.01   | 7

Answer 1

您可以使用条件聚合:

SELECT SUM(CASE WHEN type = 'RS' THEN total END) AS rstotal,
       SUM(CASE WHEN type = 'WS' THEN total END) AS wstotal,
       COUNT(code) AS "count"
FROM table1
WHERE ttime > '09-Feb-18 4:04:48 PM'

编辑 正如史蒂夫指出的那样，MS Access 不支持 CASE WHEN，因此您必须使用 IIf()功能:

SELECT SUM(IIf(type = 'RS', total, 0)) AS rstotal,
       SUM(IIf(type = 'WS', total, 0)) AS wstotal,
       COUNT(code) AS [count]
FROM table1
WHERE ttime > '09-Feb-18 4:04:48 PM'