SQL: sum 3 columns when one column has a null value without replacing null with 0?

Question

Given table:

ID   ANOTHERID      ONE    TWO      THREE
X1       B1          15    15       -
X1       B2          10    -        - 
X2       B1          -     20       -

This query:

SELECT SUM (ONE + TWO + THREE) FROM (TABLE)
GROUP BY ID, ANOTHERID

I also tried

select sum(coalesce( ONE + TWO + THREE, ONE + TWO, ONE + THREE, ONE + 
THREE))

at least one column has a null value. How can I still add them even if there is a null? As null and 0 have different meanings here (null means not started, 0 means not worked), I dont want to replace null with 0. Thanks

Why is using zero in the sum a problem since it doesn't affect the result? What effect do you want the null to have that is different to treating it as zero? (Or do you just mean you don't want to change the value in the table to zero - as opposed to just replacing it within the query?) — Alex Poole
– Alex Poole, Commented Mar 5, 2018 at 23:09
basically, the table is an employee table, ppl who havent worked in a week will have 0 and people who have recently hired but not worked (like they will start in few weeks) will have null. so, dont want to use "Use nvl(one,0)+nvl(two,0,)+nvl(three,0), etc".. Management wants to see list of people who havent worked (0 hours) and all new employees (null values) separately. — Gauravsa
– Gauravsa, Commented Mar 5, 2018 at 23:20
@Randy: it shows empty if one of them is empty but it should show null if all of them (ONE, TWO, THREE) are null. If one of them isnt null, then sum. the final sum query should return values, null, 0 separately. — Gauravsa
– Gauravsa, Commented Mar 5, 2018 at 23:24

Gordon Linoff · Accepted Answer · 2018-03-05 23:39:46Z

1

One method is:

SELECT SUM(COALESCE(ONE, 0) + COALESCE(TWO, 0) + COALESCE(THREE, 0))
FROM (TABLE)
GROUP BY ID, ANOTHERID;

Or, if you have at least one non-NULL value in each column:

SELECT SUM(ONE) + SUM(TWO) + SUM(THREE)

answered Mar 5, 2018 at 23:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user9375338 · Accepted Answer · 2018-03-06 03:11:45Z

1

The time reporting table(s) should not allow null values, and the employee table should have a hire date field which can be used as criteria in your reporting queries. This will enable you to accurately report what management expects.

edited Mar 6, 2018 at 3:11

answered Mar 6, 2018 at 2:59

user9375338

1842 silver badges10 bronze badges

Comments

Gauravsa · Accepted Answer · 2018-03-06 05:12:56Z

1

This solution worked for me

select 
case when coalesce(sum(ONE), sum(TWO), sum(THREE)) is null then null else 
sum(nvl(ONE,0) + nvl(TWO,0) + nvl(THREE,0)) end as 
TOTALSUM
GROUP BY ID, ANOTHERID;

answered Mar 6, 2018 at 5:12

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

Comments

Miracle · Accepted Answer · 2018-03-06 02:28:54Z

0

You might need to add another column in your table that describes the status of the employee (new, old) then make a condition like this:

if emp_status = 'new' then
    --some code
    working_hours := null;
else
     --some code
    working_hours : 0;
end if;

answered Mar 6, 2018 at 2:28

Miracle

3878 silver badges32 bronze badges

1 Comment

Gauravsa Over a year ago

its a fairly big software..making changes to schema is outside of this work.

Collectives™ on Stack Overflow

SQL: sum 3 columns when one column has a null value without replacing null with 0?

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related