Search code examples
sqlsumcase

How to use SQL sum if with multiple conditions

| 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
Solution

  • in your query you just missing END and When you compare any string you must you used string inside single quote '

    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