Vtyjkyuk

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

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

The reason for the NULL is when you are grouping for A with a condition of D having 2 when there is no D value as 2 for a value in column A such as a3, then it will be displayed NULL, so you can avoid that by having a derived table, hope this helps,

select A,coalesce(SUM_D1,' ') as SUM_D1 ,coalesce(SUM_D2,' ') as SUM_D2 
 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
 From table
 group by A)B 
 where SUM_D1 is not null or SUM_D2 is not null

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

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;