How to use SQL sum if with multiple conditions-CodePudding

| 1dist | 1result | 2dist | 2 result | 3dist | 3result|
|-------|---------|-------|----------|-------|--------|
|  5    |  MADE   |  NULL |   NULL   |  NULL | NULL   |
| 10    |  MISS   |  2    |   MADE   |  NULL | NULL   |
|  5    |  MADE   |  NULL |   NULL   |  NULL | NULL   |
| 20    |  MISS   |  5    |   MILL   |  2    | MADE   |

From this table I'm looking to sum the 1dist, 2dist, 3dist where the result columns = made. The result I'm looking for in the example would be 5 2 5 2 = 14

SELECT SUM(CASE WHEN 1result = MADE THEN 1dist)  
       SUM(CASE WHEN 2result = MADE THEN 2dist)  
       SUM(CASE WHEN 3result = MADE THEN 3dist)
FROM table

CodePudding user response：

Try This

SELECT SUM(CASE WHEN 1result = 'MADE' THEN 1dist END)  
SUM(CASE WHEN 2result = 'MADE' THEN 2dist END)  
SUM(CASE WHEN 3result = 'MADE' THEN 3dist END)  As Final_Result
FROM Table_Name

CodePudding user response：

There are multiple ways to do it maybe there are better ways or more optimized, try this one, i assume that table name is test. just union all the pair columns

SELECT SUM(dist) AS sum, result FROM ( SELECT [1dist] AS dist, [1result] AS result FROM test UNION ALL SELECT [2dist] AS dist, [2result] AS result FROM test
UNION ALL SELECT [3dist] AS dist, [3result] AS result FROM test ) AS a GROUP BY result HAVING (result = 'made')