Newbie here. Trying to see if there's a way to run a single query that total the values of the fields but excludes certain ones. Currently, when I run the following: Select col1,col2,col3 from table where CID in (11111,22222,33333,44444,55555)
I get:
| CID | col1 | col2 | col3 |
|---|---|---|---|
| 11111 | 10 | 20 | 40 |
| 22222 | 10 | 20 | 40 |
| 33333 | 10 | 20 | 40 |
| 44444 | 10 | 20 | 40 |
| 55555 | 10 | 20 | 40 |
Now I need to add a total but need to exclude col3 if the CID is 44444 or 55555. Something like this below:
| CID | col1 | col2 | col3 | total |
|---|---|---|---|---|
| 11111 | 10 | 20 | 40 | 70 |
| 22222 | 10 | 20 | 40 | 70 |
| 33333 | 10 | 20 | 40 | 70 |
| 44444 | 10 | 20 | 40 | 30 |
| 55555 | 10 | 20 | 40 | 30 |
Notice the total value for the last 2 only adds the value from col1 and col2. Is this even possible? The reason is the metrics for certain CID on certain columns shouldn't be counted. I have something like below but it's excluding everything for those CIDs: Select col1,col2,col3,(col1 col2 col3 where CID not in (44444,55555)) as total from table where CID in (11111,22222,33333,44444,55555)
TIA
CodePudding user response：
You may use a CASE expression to conditionally exclude the third column from the total when CID be 44444 or 55555.
SELECT CID, col1, col2, col3,
col1 col2 CASE WHEN CID NOT IN (44444, 55555) THEN col3 ELSE 0 END AS total
FROM yourTable
ORDER BY CID;