8 Replies Latest reply: May 11, 2011 1:06 PM by Ganesh Srivatsav RSS

    Case and Group by

    861228
      I've been trying to figure out what is wrong with a large sql query that won't accept the change I'm doing.
      Have managed to isolate the part that is failing but the error message I get:

      ORA-00979: not a GROUP BY expression

      is not very helpful.

      I suspect there is something fundamentally wrong in what I am trying to do.

      I've had to anonimise the table names and fields as the data I'm using is quite sensitive, but this should not detract from the problem.

      So here's the query:
      select
      case when a='100' and cost in (select cost from lookup) then '100' else b end as main,
      count(*)
      from 
      data_table
      group by 
      case when a='100' and cost in (select cost from lookup) then '100' else b end
      data_table has (amongst others) fields:
      a, b, and cost

      lookup contains the field:
      cost

      All fields are varchar2(255)


      If I remove the count(*) and entire group by statement the query runs ie:
      select
      case when a='100' and cost in (select cost from lookup) then '100' else b end as main
      from 
      data_table
      This shows the case statement is valid - so why can I not combine the count and group by?
      If it is a syntax reason is there an alternative I can use perhaps using subqueries to get around the problem - I would prefer to avoid this and can't really do the case in a decode without it getting messy.

      Oracle version:
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.

      TIA

      Edited by: user8378443 on 11-May-2011 10:26
        • 1. Re: Case and Group by
          Ganesh Srivatsav
          Try it like this,
          SELECT MAIN, COUNT (*)
            FROM (SELECT CASE
                            WHEN A = '100'
                             AND COST IN (SELECT COST FROM lookup)
                            THEN '100' ELSE b
                         END
                            AS MAIN
                    FROM data_table)
          GROUP BY MAIN
          G.
          • 2. Re: Case and Group by
            Solomon Yakobson
            with t as (
                       select case when a='100' and cost in (select cost from lookup) then '100' else b end as main from data_table
                      )
            select  main,
                    count(*)
              from  t
              group by main
            /
            SY.
            • 3. Re: Case and Group by
              861228
              I thought that was what I would have to do and was hoping there would be an easier way
              The problem is there are 2 count statements one of which is a complex count decode
              It looks like I'm going to have to bring those out of the subquery and reference the other 20 other fields in the select statement all of which will now need aliasing.
              What a drag.

              Any particular reason the syntax I was trying to use won't work without a subquery?
              • 4. Re: Case and Group by
                861228
                Your version works aswell SY
                Thanks
                • 5. Re: Case and Group by
                  Frank Kulash
                  Hi,

                  Welcome to the forum!

                  I don't know why it doesn't allow that.
                  You can do something like this:
                  WITH     got_main     AS
                  (
                       SELECT  CASE
                                WHEN  a     = '100' 
                                AND   cost      IN (
                                                   SELECT  cost 
                                             FROM    lookup
                                         ) 
                                THEN  '100' 
                                ELSE  b 
                               END          AS main
                       FROM    data_table
                  )
                  SELECT       main
                  ,       COUNT (*)     AS cnt
                  FROM       got_main
                  GROUP BY  main
                  ;
                  You'd probably want to factor out the CASE expression, anyway, just to make the code easier to debug and maintain.
                  • 6. Re: Case and Group by
                    AlanWms
                    Well, this runs fine for me:
                    with data_table as (
                    select 1 id, '100' a, '201' b, 1.00 cost from dual
                    union all
                    select 1 id, '100' a, '202' b, 2.00 cost from dual
                    union all
                    select 1 id, '200' a, '203' b, 3.00  cost from dual
                    )
                    ,
                    lookup as
                    (
                    select 2.00 cost from dual
                    )
                    
                    select
                    case when a='100' and cost in (select cost from lookup) then '100' else b end as main,
                    count(*)
                    from
                    data_table
                    group by
                    case when a='100' and cost in (select cost from lookup) then '100' else b end
                    Result:
                    MAIN     COUNT(*)
                    100     1
                    201     1
                    203     1
                    I suspect your anonymized code does not reflect your actual problem.
                    • 7. Re: Case and Group by
                      861228
                      I ran your query and it gives the not a group by expression error.

                      Have managed to find a way that doesn't involve realiasing the tables so all is good - just took me the best part of an hour rather than the 10 minutes it should've taken.

                      Thanks all for the help and the welcome should've said hello first!

                      Still a little bemused why it doesn't work and suspect it's due to the oracle version since Alan got his to work.
                      Also tried the same query on an old oracle 9i database and it gave the same error.
                      • 8. Re: Case and Group by
                        Ganesh Srivatsav
                        The query alternanitive can be written like this without a subquery,
                        SELECT CASE
                                  WHEN A = '100' AND d.COST = l.COST THEN '100'
                                  ELSE  b
                               END AS MAIN,
                               COUNT (*)
                          FROM data_table d, (SELECT DISTINCT COST FROM lookup) l
                         WHERE d.COST = l.COST(+)
                        GROUP BY CASE
                                  WHEN A = '100' AND d.COST = l.COST THEN '100'
                                  ELSE  b
                               END;
                        Anf for all others, A testcase difference between versions.

                        In 9i,
                        Connected to:
                        Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
                        With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
                        JServer Release 9.2.0.8.0 - Production
                        
                        SQL> CREATE TABLE T AS SELECT LEVEL col1,LEVEL+1 col2 FROM dual connect BY LEVEL<=100
                          2  /
                        
                        Table created.
                        
                        SQL> SELECT CASE WHEN col1 IN (SELECT col2 FROM T) THEN 1 ELSE 2 END, COUNT (*)
                          2    FROM T
                          3  GROUP BY CASE WHEN col1 IN (SELECT col2 FROM T) THEN 1 ELSE 2 END;
                        SELECT CASE WHEN col1 IN (SELECT col2 FROM T) THEN 1 ELSE 2 END, COUNT (*)
                                         *
                        ERROR at line 1:
                        ORA-00979: not a GROUP BY expression
                        In 11g,
                        Connected to:
                        Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                        With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
                        OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
                        
                        SQL> CREATE TABLE T AS SELECT LEVEL col1,LEVEL+1 col2 FROM dual connect BY LEVEL<=100
                          2  /
                        
                        Table created.
                        
                        SQL> SELECT CASE WHEN col1 IN (SELECT col2 FROM T) THEN 1 ELSE 2 END, COUNT (*)
                          2    FROM T
                          3  GROUP BY CASE WHEN col1 IN (SELECT col2 FROM T) THEN 1 ELSE 2 END;
                        
                        CASEWHENCOL1IN(SELECTCOL2FROMT)THEN1ELSE2END   COUNT(*)
                        -------------------------------------------- ----------
                                                                   1         99
                                                                   2          1
                        
                        SQL> 
                        Edited by: Ganesh Srivatsav on May 11, 2011 2:05 PM