Regarding group by - Oracle 10g

Using Oracle10g. I am using "case when" in a GROUP BY clause. The problem is when I use:

a.book_name IN (SELECT BOOK 
                  FROM BOOK_CONF)

It's saying it's not a GROUP BY clause. Can anyone help me regarding this?

  SELECT pub_name, 
         CASE 
           WHEN a.book_name IN (SELECT BOOK FROM BOOK_CONF) THEN 'UNI'   
           WHEN a.book_name ='JAVA' THEN 'JAVA'    
 开发者_开发知识库          ELSE 'MIS'    
         END) test, 
         COUNT(*) 
    FROM books a 
GROUP BY pub_name, 
         CASE 
           WHEN a.book_name in (SELECT BOOK FROM BOOK_CONF) THEN 'UNI'
           WHEN a.book_name ='JAVA' THEN 'JAVA'    
         ELSE 'MIS'

---

The earliest Oracle allows referencing column aliases is ORDER BY - you'll get ORA-00904: Invalid Identifier otherwise (IME that is, up to and incl. 10g - dunno if that changed in 11g). So the easiest way to accommodate that is to use a derived table/inline view:

  SELECT x.pub_name,
         x.test,
         COUNT(*)
    FROM (SELECT a.pub_name, 
                 CASE 
                   WHEN a.book_name IN (SELECT BOOK FROM BOOK_CONF) THEN 'UNI'   
                   WHEN a.book_name ='JAVA' THEN 'JAVA'    
                   ELSE 'MIS'    
                 END AS test
            FROM BOOKS a) x
GROUP BY x.pub_name, x.test

---

Please allow me to summarize because there is some good and some bad advice here mixed together.

First your tables with some sample data:

SQL> create table books (book_name,pub_name)
  2  as
  3  select 'JAVA', 'PUB1' from dual union all
  4  select 'JAVA', 'PUB2' from dual union all
  5  select 'ABC', 'PUB1' from dual union all
  6  select 'XYZ', 'PUB3' from dual union all
  7  select 'KLM', 'PUB3' from dual
  8  /

Table created.

SQL> create table book_conf (book)
  2  as
  3  select 'XYZ' from dual union all
  4  select 'KLM' from dual
  5  /

Table created.

Your query:

SQL> SELECT pub_name
  2       , CASE
  3         WHEN a.book_name IN (SELECT BOOK FROM BOOK_CONF) THEN
  4           'UNI'
  5         WHEN a.book_name ='JAVA' THEN
  6           'JAVA'
  7         ELSE
  8           'MIS'
  9         END test
 10       , COUNT(*)
 11    FROM books a
 12   GROUP BY pub_name
 13       , CASE
 14         WHEN a.book_name in (SELECT BOOK FROM BOOK_CONF) THEN
 15           'UNI'
 16         WHEN a.book_name ='JAVA' THEN
 17           'JAVA'
 18         ELSE
 19           'MIS'
 20         END
 21  /
       WHEN a.book_name IN (SELECT BOOK FROM BOOK_CONF) THEN
            *
ERROR at line 3:
ORA-00979: not a GROUP BY expression

This is NOT because a CASE expression is not allowed in the group by clause. It's because you are using a SELECT statement inside your GROUP BY clause. If you replace the " IN (SELECT book FROM book_conf)" with " IN ('XYZ','KLM')", you'll see that your statement now succeeds:

SQL> SELECT pub_name
  2       , CASE
  3         WHEN a.book_name IN ('XYZ','KLM') THEN
  4           'UNI'
  5         WHEN a.book_name ='JAVA' THEN
  6           'JAVA'
  7         ELSE
  8           'MIS'
  9         END test
 10       , COUNT(*)
 11    FROM books a
 12   GROUP BY pub_name
 13       , CASE
 14         WHEN a.book_name in ('XYZ','KLM') THEN
 15           'UNI'
 16         WHEN a.book_name ='JAVA' THEN
 17           'JAVA'
 18         ELSE
 19           'MIS'
 20         END
 21  /

PUB_ TEST   COUNT(*)
---- ---- ----------
PUB1 MIS           1
PUB1 JAVA          1
PUB2 JAVA          1
PUB3 UNI           2

4 rows selected.

The query below looks great, doesn't produce an error, but gives a different result because you are now grouping by book_name instead of your CASE expression:

SQL> select a.pub_name
  2       , (CASE WHEN a.book_name in (SELECT BOOK FROM BOOK_CONF) THEN 'UNI'
  3               WHEN a.book_name ='JAVA' THEN 'JAVA'
  4               ELSE 'MIS'
  5          END) test
  6       , count(*)
  7    from books a
  8   group by a.pub_name
  9       , a.book_name
 10  /

PUB_ TEST   COUNT(*)
---- ---- ----------
PUB1 MIS           1
PUB1 JAVA          1
PUB2 JAVA          1
PUB3 UNI           1
PUB3 UNI           1

5 rows selected.

OMG Ponies uses the right approach by encapsulating the group by expression in an inline view:

SQL> select pub_name
  2       , book_type
  3       , count(*)
  4    from ( select pub_name
  5                , case
  6                  when book_name in (select book from book_conf) then
  7                    'UNI'
  8                  when book_name = 'JAVA' then
  9                    'JAVA'
 10                  else
 11                    'MIS'
 12                  end book_type
 13             from books
 14         )
 15   group by pub_name
 16       , book_type
 17  /

PUB_ BOOK   COUNT(*)
---- ---- ----------
PUB1 MIS           1
PUB1 JAVA          1
PUB2 JAVA          1
PUB3 UNI           2

4 rows selected.

Hope this helps.

Regards,
Rob.

---

select pub_name,
(CASE   WHEN a.book_name in(SELECT BOOK FROM BOOK_CONF) THEN 'UNI'  
 WHEN a.book_name ='JAVA' THEN 'JAVA'    ELSE 'MIS'    END)  AS test,
count(*) from books a group by pub_name, test;

I believe you have not include test in the group by clause. Let me know if that works

---

Since a.book_name is what you are testing for inside of the case, that is what you should have in the group by:

select a.pub_name, (CASE WHEN a.book_name in (SELECT BOOK FROM BOOK_CONF) THEN 'UNI'   
                       WHEN a.book_name ='JAVA' THEN 'JAVA'    
                       ELSE 'MIS'    
                  END) test, count(*) 
from books a 
group by a.pub_name, a.book_name