CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Duy Huynh
Duy Huynh

Reputation: 291

PostgreSQL - Removing NULLS row and column from conditional aggregation results

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

Upvotes: 0

Views: 3166

Answers (4)

dnoeth
dnoeth

Reputation: 60462

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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;

Upvotes: 2

dwir182
dwir182

Reputation: 1549

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

Upvotes: 1

johey
johey

Reputation: 1219

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? :-)

Upvotes: 1

Related Questions