PostgreSQL - Removing NULLS row and column from conditional aggregation results

Question

I have a query for a multidimensional table using conditional aggregation

select A,
   SUM(case when D = 3 then D end) as SUM_D1,
   SUM(case when D = 4 then D end) as SUM_D2)

The result:

A    SUM_D1  SUM_D2
-------------------
a1   100     NULL
a1   200     NULL
a3   NULL    NULL
a4   NULL    NULL

However, I would like to hide all NULL rows and columns as follows:

A   SUM_D1 
-----------
a1   100 
a1   200

I have looked for similar problems but they are not my expected answer.

Any help is much appreciated,

Thank you

Use having to filter for IS NOT NULL or where plus Derived Table. For not showing columns there's no way using plain SQL. — dnoeth
– dnoeth, Commented Nov 9, 2018 at 8:36

Gordon Linoff · Accepted Answer · 2018-11-09 12:06:26Z

2

I think this does what you want:

select A,
       coalesce(sum(case when D = 3 then D end),
                sum(case when D = 4 then D end)
               ) as sum_d
from t
group by A
having sum(case when d in (3, 4) then 1 else 0 end) > 0;

Note that this returns only one column -- as in your example. If both "3" and "4" are in the data, then the value is for the "3"s.

If you want a query that returns a variable number of columns, then you need to use dynamic SQL -- or some other method. SQL queries return a fixed number of columns.

One method would be to return the values as an array:

select a,
       array_agg(d order by d) as ds,
       array_agg(sumd order by d) as sumds
from (select a, d, sum(d) as sumd
      from t
      where d in (3, 4)
      group by a, d
     ) d
group by a;

answered Nov 9, 2018 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Duy Huynh Over a year ago

Thank you, I think a having clause in your expression maybe helps me to hide NULL rows. but I think it is not possible for hiding an empty column.

Gordon Linoff Over a year ago

@DuyHuynh . . . You cannot "hide" columns, unless you use dynamic SQL. The columns are explicitly specified in the SELECT.

dnoeth · Accepted Answer · 2018-11-12 09:07:07Z

To filter all-NULL rows you can use HAVING

select *
from 
 (
    select A,
       SUM(case when D = 3 then D end) as SUM_D1,
       SUM(case when D = 4 then D end) as SUM_D2)
    ...
 ) as dt
where SUM_D1 is not null 
  and SUM_D2 is not null

Of course, if you got simple conditions like the ones in your example you better filter before aggregation:

select A,
   SUM(case when D = 3 then D end) as SUM_D1,
   SUM(case when D = 4 then D end) as SUM_D2)
...
where D in (3,4)

Now at least one calculation will return a value, thus no need to check for all-NULL.

To filter all-NULL columns you need some Dynamic SQL:

materialize the data in a temporary tabke using Insert/Select
scan each column for all-NULL select 1 from temp having count(SUM_D1) > 0
dynamically create the Select list based on this
run the Select

But why do you think you need this? It will be confusing for a user to run the same Stored Procedure and receive a different number of columns for each run.

johey · Accepted Answer · 2018-11-09 09:24:35Z

1

I may have misinterpreted your question because the solution seems so simple:

select A,
   SUM(case when D = 3 then D end) as SUM_D1,
   SUM(case when D = 4 then D end) as SUM_D2)
where D is not null

This is not what you want, is it? :-)

answered Nov 9, 2018 at 9:24

johey

1,2181 gold badge11 silver badges27 bronze badges

1 Comment

Duy Huynh Over a year ago

In my case, I think D is not null always true because D = {3,4}. The values is null because D=3 and A =a3 is null and so on.

dwir182 · Accepted Answer · 2018-11-09 11:25:30Z

1

Null appear because the condition that's not handled by case statement

select A,
   SUM(case when D = 3 then D end) as SUM_D1,
   SUM(case when D = 4 then D end) as SUM_D2
 from
    Table1
 group by
     A
 having 
     (case when D = 3 or D = 4 then D end) is not null

As comment said if you want to suppress the null value.. You can use having to suppress null using is not null

edited Nov 9, 2018 at 11:25

answered Nov 9, 2018 at 8:29

dwir182

1,54911 silver badges21 bronze badges

5 Comments

dnoeth Over a year ago

The OP doesn't want to disply zero instead of NULL, he wants to suppress those rows/columns

dwir182 Over a year ago

Yes.. D have that both value and i miss the op want.. Op want to suppress those.. Not change value..

dnoeth Over a year ago

having D is not null will not run, after aggregation there's no more individual D, must be in WHERE

Duy Huynh Over a year ago

In your expression, if 3 and 4 exist in D the having clause is always true, is it right? I think the null value in my case comes from the value D =3 and A = a3 does not exist

dwir182 Over a year ago

@DuyHuynh your case when d= 3 or d=4 will show d and if no then value become null because you not handle the condition if d != 3 or d != 4.. And i filter that with having and of course a3 will not exist because he don't have condition met..

Collectives™ on Stack Overflow

PostgreSQL - Removing NULLS row and column from conditional aggregation results

4 Answers 4

2 Comments

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related