Hi,
I have four columns in group by clause. I want to add conditional group by clause.
Case 1 : If one of the column's value is null, I don't want to include it in group by clause. Means, Now GROUP BY clause will have only 3 columns.
Case 2 : If not null, then GROUP BY clause with all four columns.
Please help me out on this.
Thanks in advance.
You want a SQL or are you doing this in PL/SQL?
I want SQL which will satisfy both cases.
Supply a test case, expected results and logical explanation.
Can't be done with a single query. You can have two queries. One which gets all the rows where the last column is null and does a group by 3 columns and a second query which gets all the ones where the last column is not null and has a group by all four columns
SELECT c1,c2,c3 FROM t1 WHERE c4 IS NULL GROUP BY c1, c2, c3;
SELECT c1,c2,c3,c4 FROM t1 WHERE c4 IS NOT NULL GROUP BY c1, c2, c2, c4;
And what would be the difference ?
The lines with null values are being groupped together... is it not what you are expecting ?
SQL> with t as (
2 select 3 n, 2 m from dual
3 union all select 3 n, 2 m from dual
4 union all select 3 n, null m from dual
5 union all select 3 n, null m from dual )
6 select n, m, count(*)
7 from t
8 group by n, m;
N M COUNT(*)
---------- ---------- ----------
3 2
3 2 2
Hi
I think it won't matter, all group functions by default ignore NULLs so your result won't differ.
select dept, loc, sum(sal)
from (
select 'A' emp , 1 dept , 'P' loc , 100 sal from dual union all
select'B',1,'P',200 from dual union all
select'C',2,'P',300 from dual union all
select'D',2,'P',400 from dual union all
select'E',3, 'P',500 from dual union all
select'F',3, 'P',600 from dual union all
select'G',4, 'Q',700 from dual union all
select'H', null,'Q' , 1000 from dual union all
select'I',null ,'Q', 2000 from dual union all
select 'J' ,null, 'Q',300 from dual)
group by dept,loc;
Output
------------------------------------------
DEPT LOC SUM(SAL)
1 P 300
2 P 700
3 P 1100
Q 3300
4 Q 700
Now by doing grouping only for NOT NULL values,
select dept,loc, sum(sal)
from (
select 'A' emp , 1 dept , 'P' loc , 100 sal from dual union all
select'B',1,'P',200 from dual union all
select'C',2,'P',300 from dual union all
select'D',2,'P',400 from dual union all
select'E',3, 'P',500 from dual union all
select'F',3, 'P',600 from dual union all
select'G',4, 'Q',700 from dual union all
select'H', null,'Q' , 1000 from dual union all
select'I',null ,'Q', 2000 from dual union all
select 'J' ,null, 'Q',300 from dual)
where dept is not null --------------NOT NULL Condition
group by dept, loc;
Output
---------------
DEPT LOC SUM(SAL)
1 P 300
2 P 700
3 P 1100
4 Q 700
Now by doing grouping only for NULL values,
select dept,loc, sum(sal)
from (
select 'A' emp , 1 dept , 'P' loc , 100 sal from dual union all
select'B',1,'P',200 from dual union all
select'C',2,'P',300 from dual union all
select'D',2,'P',400 from dual union all
select'E',3, 'P',500 from dual union all
select'F',3, 'P',600 from dual union all
select'G',4, 'Q',700 from dual union all
select'H', null,'Q' , 1000 from dual union all
select'I',null ,'Q', 2000 from dual union all
select 'J' ,null, 'Q',300 from dual)
where dept is null --------------NULL Condition
group by dept, loc;
Output
---------------
DEPT LOC SUM(SAL)
Q 3300
The output is same for both the conditions.
Re: 2. How do I ask a question on the forums?
You don't describe what you want. You are describing a possible solution, which might be wrong. We can give much better advice as soon as you are able to
a) describe the business case
b) give input data (small but complete test)
c) give expected outcome