This discussion is archived
7 Replies Latest reply: Jul 26, 2013 4:51 AM by Sven W. RSS

conditional GROUP BY clause

987705 Newbie
Currently Being Moderated

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.  

  • 1. Re: conditional GROUP BY clause
    Lalit Kumar B Explorer
    Currently Being Moderated

    You want a SQL or are you doing this in PL/SQL?

  • 2. Re: conditional GROUP BY clause
    987705 Newbie
    Currently Being Moderated

    I want SQL which will satisfy both cases.

  • 3. Re: conditional GROUP BY clause
    Pablolee Journeyer
    Currently Being Moderated

    Supply a test case, expected results and logical explanation.

  • 4. Re: conditional GROUP BY clause
    kendenny Expert
    Currently Being Moderated

    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;


  • 5. Re: conditional GROUP BY clause
    Pacmann Journeyer
    Currently Being Moderated

    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

  • 6. Re: conditional GROUP BY clause
    myOra_help Journeyer
    Currently Being Moderated

    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.

     

  • 7. Re: conditional GROUP BY clause
    Sven W. Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points