calculate Row Wise Sum - Sql server

Question

This is My table : ``` ID Q1 Q2 Q3 Q4 ---------------------------------------------------------------- 20130712 NULL 728.63 NULL NULL 20130712 8881.55 9673.68 2629.566 6251.984 20130713 1813 1813 84.49 1728.51 20130714 3632.65 3632.65 1209.412 2423.238 20130714 70.758 2637.43 70.758 0 20130714 1601.578 3569.73 204.745 1396.833 20130714 728.63 728.63 0 728.63 20130714 1401.629 2251.39 94.418 1307.211 20130715 583.956 5089.19 583.956 0 20130805 6317.277 8958 2629.566 3687.711 ``` I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise) ``` ID Q1 Q2 Q3 Q4 SUM(Q1:Q4) --------------------------------------------------------------------------- 20130712 NULL 728.63 NULL NULL 728.63 20130712 8881.55 9673.68 2629.566 6251.984 27436.78 20130713 1813 1813 84.49 1728.51 5439 20130714 3632.65 3632.65 1209.412 2423.238 ... 20130714 70.758 2637.43 70.758 0 20130714 1601.578 3569.73 204.745 1396.833 20130714 728.63 728.63 0 728.63 20130714 1401.629 2251.…

Accepted Answer

You haven't shown your query attempt, but it's probably something like this:

```
SELECT
  ID, Q1, Q2, Q3, Q4,
  Q1 + Q2 + Q3 + Q4 AS "Total"
FROM MyTable
```

If any of the `Q1`, `Q2`, `Q3`, or `Q4` values are null, `Q1 + Q2 + Q3 + Q4` will be null. To treat the nulls as zero and get a proper sum, do this instead:

```
SELECT
  ID, Q1, Q2, Q3, Q4,
  COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0) AS "Total"
FROM MyTable
```

The `COALESCE` function will return the first non-null value in the list.

calculate Row Wise Sum - Sql server

Solution

Problem