CODEWITHSUNDEEP
sqlpostgresql
Foad Tahmasebi
Foad Tahmasebi

Reputation: 1342

How to group by a column with null values

I have a table like this:

C1   | C2
-----|----
23   | 10
null | 10
23   | 10
24   | 10
24   | 10

I want group by C1 and calculate null's C2 value in both 23 and 24's sum(C2)

SELECT C1, sum(C2) FROM table 
GROUP BY C1

query return this table:

C1   | C2
-----|----
23   | 20
null | 10
24   | 20

but I want this:

C1   | C2
-----|----
23   | 30
24   | 30

is it possible to get a result like that?

Upvotes: 1

Views: 417

Answers (2)

dchar
dchar

Reputation: 1695

This should work (I tested in MySQL):

SELECT C1, sum(C2) + @runtot AS sum
FROM (SELECT @runtot:= SUM(C2) from mytable t where t.C1 IS NULL) c,
mytable
WHERE C1 IS NOT NULL
GROUP BY C1

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Do a GROUP BY as before, but do also add a sub-select that sums c2 for all null valued c1's:

select c1, sum(c2) + (select sum(c2) from table where c1 is null)
from table
where c1 is not null
group by c1

Core-99 ANSI SQL.

Upvotes: 2

Related Questions