根据同一表中的其他列值计算和(列)

Question

我有下表

Id   quantity    level_1     group_1
1    13          1           1
2    10          1           1
3    10          0           1
4    10          0           1
5    20          1           0

我想忽略上表中的_1=‘0’。

本文分别计算了满足上述条件的群1=‘1’和群1=‘0’的和(量)。

预期结果：

sum_quantity   sum_quantity_2_Not_Valid
23             20  

Answer 1

试试下面的样子

模式：

CREATE TABLE #TAB (Id INT, quantity INT, level_1 INT, group_1 INT)
INSERT INTO #TAB

SELECT 1, 13, 1, 1
UNION ALL
SELECT 2, 10, 1, 1
UNION ALL
SELECT 3, 10, 0, 1
UNION ALL
SELECT 4, 10, 0, 1
UNION ALL
SELECT 5, 20, 1, 0

将SUM与CASE结合使用

SELECT  SUM(CASE level_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
, SUM(CASE level_1 WHEN 0 THEN quantity ELSE 0 END) AS sum_quantity_2_Not_Valid 
FROM #TAB
WHERE group_1 =1

结果将是

+--------------+--------------------------+
| sum_quantity | sum_quantity_2_Not_Valid |
+--------------+--------------------------+
|           23 |                       20 |
+--------------+--------------------------+

如果您打算通过分组group_1获得值

SELECT [1] AS sum_quantity, [0] AS sum_quantity_2_Not_Valid  FROM (
SELECT group_1,
SUM(CASE level_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
FROM #TAB
GROUP BY group_1
)AS A
PIVOT
(
    MAX( sum_quantity) FOR group_1 IN ([1],[0])
)PVT

Answer 2

Use SUB query to get result :

CREATE TABLE #Table(Id INT,  quantity INT, level_1 INT, group_1 INT)
INSERT INTO #Table(Id ,  quantity , level_1 , group_1)
SELECT 1,13,1,1 UNION ALL
SELECT 2,10,1,1 UNION ALL
SELECT 3,10,0,1 UNION ALL
SELECT 4,10,0,1 UNION ALL
SELECT 5,20,1,0

SELECT SUM(A.quantity) sum_quantity,SUM(B.quantity) sum_quantity_2_Not_Valid 
FROM 
(
   SELECT SUM(quantity) quantity,group_1
   FROM #Table
   WHERE level_1 = 1 AND group_1 = 1
   GROUP BY group_1
 )A,
 (
   SELECT SUM(quantity) quantity,group_1
   FROM #Table
   WHERE level_1 = 1 AND group_1 = 0
   GROUP BY group_1
  )B

Answer 3

这将是答案，因为他希望排除_1=‘0’：

SELECT SUM(CASE group_1 WHEN 1 THEN quantity ELSE 0 END) AS sum_quantity
     , SUM(CASE group_1 WHEN 0 THEN quantity ELSE 0 END) AS sum_quantity_2_Not_Valid 
FROM table
WHERE level_1 <> 0