Oracle SQL use of subquery simulateneously in group by & select clauses in conjunction with CASE operator

Question

Long title and strange problem: I want to use the with-statement in oracle SQL to reuse a sub-query as well in the select as group by clause. Additionally, I use a case statement in order to create more information and group the results. This statement however throws following error: ORA-00979: not a GROUP BY expression.

Example query that is not working: I define a query containing the sum of the sales per product family. I sort this query and select the best selling product family out if it. As main result, I want to compare this top selling family to the sum of the sales of other product families (not one by one but all other product families grouped together). I do this following way:

WITH
top_family AS (
SELECT *
FROM (SELECT c.family
      FROM products c, sales d
      WHERE c.product_id=  d.product_id
      GROUP BY c.family
      ORDER BY SUM(d.quantity) DESC) 
      WHERE ROWNUM = 1)

SELECT CASE
           WHEN a.family IN (SELECT * FROM top_family)
           THEN 'Most sold category'
           ELSE 'Other categories'
       END Family, SUM(a.price*b.quantity) "Total monetary sales"
FROM products a, sales b
WHERE a.product_id =  b.product_id
GROUP BY CASE
             WHEN a.family IN (SELECT * FROM top_family)
             THEN 'Most sold category'
             ELSE 'Other categories'
         END
ORDER BY 1;

An interesting fact is that if I replace the sub-query 'top_family' as defined in the code above directly into the code (so replace every every place containing top_family with the select * from (select ...) statement), it works and gives the desired result.

The problem should probably be caused by using the sub-query defined in a with statement. Although I realize there are (better and more elegant) solutions than this one, I'd like to find out why I can't use the table alias "top_family" in the group by and select statement.

Solution

The problem is in the GROUP BY CASE WHEN statement.

This statement is only compiled in the final step of execution. This way that sub-clause is witheld from the SELECT CASE WHEN. This null operation is returning errors.

It is also described in the SQL manual.

Comments

[Drizzt]

Strange enough, this code is missing a right parenthesis somewhere in the with clause. I edited my questions a bit: do you happen to know why my code isn't running?

[Rachcha]

Well, in that case, you the right parenthesis before the main SELECT statement.