Cannot use Max with Count in SQL*Plus

Question

this is my sql statement i get this error. but when i use only Max to single and without displaying other results it works. can someone help me

SELECT cat.CategoryName,sb.SubCategoryName,MAX((COUNT(bs.BookID))) 
FROM
  Category cat,SubCategory sb, Book_Subcategory bs 
WHERE cat.CategoryID = sb.CategoryID AND sb.SubCategoryID = bs.SubCategoryID 
GROUP BY cat.CategoryName, sb.SubCategoryName, bs.BookID;

ERROR at line 1:
ORA-00937: not a single-group group function

Can someone help me?

Answer 1

SQL does not allow aggregates of aggregates directly.

However, if you write the inner aggregate in a sub-query in the FROM clause (or use a WITH clause and a Common Table Expression, CTE), you can achieve the result:

SELECT gc1.CategoryName, gc1.SubCategoryName, gc1.BookCount
  FROM (SELECT cat.CategoryName, sb.SubCategoryName,
               COUNT(bs.BookID) AS BookCount
          FROM Category AS cat
          JOIN SubCategory AS sb ON cat.CategoryID = sb.CategoryID
          JOIN Book_Subcategory AS bs ON sb.SubCategoryID = bs.SubCategoryID
         GROUP BY cat.CategoryName, sb.SubCategoryName
       ) AS gc1
 WHERE gc1.BookCount = (SELECT MAX(gc2.BookCount)
                          FROM (SELECT cat.CategoryName, sb.SubCategoryName,
                                       COUNT(bs.BookID) AS BookCount
                                  FROM Category AS cat
                                  JOIN SubCategory AS sb
                                       ON cat.CategoryID = sb.CategoryID
                                  JOIN Book_Subcategory AS bs
                                       ON sb.SubCategoryID = bs.SubCategoryID
                                 GROUP BY cat.CategoryName, sb.SubCategoryName
                                ) AS gc2
                        )

This is complex because it doesn't use a CTE, and there is a common table expression that must be written out twice.

Using the CTE form (possibly with syntax errors):

WITH gc1 AS (SELECT cat.CategoryName, sb.SubCategoryName,
                    COUNT(bs.BookID) AS BookCount
               FROM Category AS cat
               JOIN SubCategory AS sb
                    ON cat.CategoryID = sb.CategoryID
               JOIN Book_Subcategory AS bs
                    ON sb.SubCategoryID = bs.SubCategoryID
              GROUP BY cat.CategoryName, sb.SubCategoryName
            )
SELECT gc1.CategoryName, gc1.SubCategoryName, gc1.BookCount
  FROM gc1
 WHERE gc1.BookCount = SELECT MAX(gc1.BookCount) FROM gc1);

Much tidier!

You can simulate a CTE with a temporary table if your DBMS makes it easy to create them. For example, IBM Informix Dynamic Server could use:

SELECT cat.CategoryName, sb.SubCategoryName,
       COUNT(bs.BookID) AS BookCount
  FROM Category AS cat
  JOIN SubCategory AS sb ON cat.CategoryID = sb.CategoryID
  JOIN Book_Subcategory AS bs ON sb.SubCategoryID = bs.SubCategoryID
 GROUP BY cat.CategoryName, sb.SubCategoryName
  INTO TEMP gc1;

SELECT gc1.CategoryName, gc1.SubCategoryName, gc1.BookCount
  FROM gc1
 WHERE gc1.BookCount = (SELECT MAX(gc1.BookCount) FROM gc1);

DROP TABLE gc1;  -- Optional: table will be deleted at end of session anyway

---

Given the following tables and data, the main query (copied and pasted from this answer) gave the result I expected when run against IBM Informix Dynamic Server 11.50.FC6 on MacOS X 10.6.4, namely:

Non-Fiction    SQL Theory    4
Fiction        War           4

That doesn't prove that it 'must work' when run against Oracle - I don't have Oracle and can't demonstrate either way. It does show that there is at least one SQL DBMS that handles the query without problems. (Since IDS does not support the WITH clause and CTEs, I can't show whether that formulation works.)

Schema

CREATE TABLE Category
(
    CategoryID INTEGER NOT NULL PRIMARY KEY,
    CategoryName VARCHAR(20) NOT NULL
);
CREATE TABLE SubCategory
(
    CategoryID INTEGER NOT NULL REFERENCES Category,
    SubCategoryID INTEGER NOT NULL PRIMARY KEY,
    SubCategoryName VARCHAR(20) NOT NULL
 );
CREATE TABLE Book_SubCategory
(
    SubCategoryID INTEGER NOT NULL REFERENCES SubCategory,
    BookID INTEGER NOT NULL PRIMARY KEY
);

Data

INSERT INTO Category VALUES(1, 'Fiction');
INSERT INTO Category VALUES(2, 'Non-Fiction');

INSERT INTO SubCategory VALUES(2, 1, 'SQL Theory');
INSERT INTO SubCategory VALUES(2, 2, 'Mathematics');
INSERT INTO SubCategory VALUES(1, 3, 'Romance');
INSERT INTO SubCategory VALUES(1, 4, 'War');

INSERT INTO Book_SubCategory VALUES(1, 10);
INSERT INTO Book_SubCategory VALUES(2, 11);
INSERT INTO Book_SubCategory VALUES(3, 12);
INSERT INTO Book_SubCategory VALUES(3, 13);
INSERT INTO Book_SubCategory VALUES(4, 14);
INSERT INTO Book_SubCategory VALUES(1, 15);
INSERT INTO Book_SubCategory VALUES(1, 16);
INSERT INTO Book_SubCategory VALUES(2, 17);
INSERT INTO Book_SubCategory VALUES(1, 18);
INSERT INTO Book_SubCategory VALUES(3, 19);
INSERT INTO Book_SubCategory VALUES(4, 20);
INSERT INTO Book_SubCategory VALUES(4, 21);
INSERT INTO Book_SubCategory VALUES(4, 22);