Getting the count of groups from a GROUP BY query – without using subquery

As an example, I have a relation called STUDENT with the following attributes:

SID (primary key), NAME, FACULTY, AGE, MAJOR

I want to retrieve how many times there are more than 10 students with the same FACULTY, AGE and MAJOR (these attributes are non-key and non-unique).

I’m able to write a query for this, but I don’t want to be selecting from a subquery.

I have a solution to this which is as follows:

SELECT COUNT(*) 
FROM
   (SELECT FACULTY, AGE, MAJOR, COUNT(*)
    FROM STUDENT
    GROUP BY FACULTY, AGE, MAJOR
    HAVING COUNT(*) > 10) students

This gets me the expected result, however I want to write a query that doesn’t involve selecting from a subquery. Is there any alternative query to what I’ve given?

Answer

There doesn’t really seem to be anything wrong with what you are doing; but you could do:

SELECT COUNT(COUNT(*))
FROM STUDENT
GROUP BY FACULTY, AGE, MAJOR
HAVING COUNT(*) > 10;

That is, using nested aggregation; there’s also an example of this in the documentation.

We don’t have your data, so demo with the HR sample-schema employee table:

select count(*)
from (
  select department_id, count(*)
  from  employees
  group by department_id
  having count(*) > 5
);

  COUNT(*)
----------
         4

select count(count(*))
from employees
group by department_id
having count(*) > 5;

COUNT(COUNT(*))
---------------
              4

Having the subquery makes it a bit clearer what you are doing though.

---

You could also use subquery factoring I suppose, but as the name suggests that’s still a subquery – whether you have the same objection to that is unclear though:

WITH students (FACULTY, AGE, MAJOR, c) AS (
  SELECT FACULTY, AGE, MAJOR, COUNT(*)
  FROM STUDENT
  GROUP BY FACULTY, AGE, MAJOR
  HAVING COUNT(*) > 10
)
SELECT COUNT(*) 
FROM students;

or

WITH students (FACULTY, AGE, MAJOR, c) AS (
  SELECT FACULTY, AGE, MAJOR, COUNT(*)
  FROM STUDENT
  GROUP BY FACULTY, AGE, MAJOR
)
SELECT COUNT(*) 
FROM students
WHERE c > 10;

Demo again:

with cte (departmenr_id, c) as (
  select department_id, count(*)
  from  employees
  group by department_id
  having count(*) > 5
)
select count(*)
from cte;

  COUNT(*)
----------
         4

with cte (departmenr_id, c) as (
  select department_id, count(*)
  from  employees
  group by department_id
)
select count(*)
from cte
where c > 5;

  COUNT(*)
----------
         4

You don’t actually need FACULTY, AGE, MAJOR, in the subquery, in either form (your original or using a CTE) since you don’t use them outside; you can group by expression you don’t select.