How To Count Groups Returned with the GROUP BY Clause?
Page 1 of 1
How To Count Groups Returned with the GROUP BY Clause?
If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the
number of rows within each group, not the number of groups. If you want to count the number of
groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on
the main query as shown in the following tutorial exercise:
SQL> SELECT first_name, COUNT(*) FROM employees
GROUP BY first_name HAVING COUNT(*) > 1;
FIRST_NAME COUNT(*)
-------------------- ----------
Peter 3
Michael 2
Steven 2
John 3
Julia 2
William 2
Karen 2
Kevin 2
......
SQL> SELECT COUNT(*) FROM (
SELECT first_name, COUNT(*) FROM employees
GROUP BY first_name HAVING COUNT(*) > 1
);
COUNT(*)
----------
13
number of rows within each group, not the number of groups. If you want to count the number of
groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on
the main query as shown in the following tutorial exercise:
SQL> SELECT first_name, COUNT(*) FROM employees
GROUP BY first_name HAVING COUNT(*) > 1;
FIRST_NAME COUNT(*)
-------------------- ----------
Peter 3
Michael 2
Steven 2
John 3
Julia 2
William 2
Karen 2
Kevin 2
......
SQL> SELECT COUNT(*) FROM (
SELECT first_name, COUNT(*) FROM employees
GROUP BY first_name HAVING COUNT(*) > 1
);
COUNT(*)
----------
13
Similar topics
» Can Group Functions Be Used in the ORDER BY Clause?
» Can Group Functions Be Mixed with Non-group Selection Fields?
» How To Use Subqueries in the FROM clause?
» How To Divide Query Output into Groups?
» How To Write an Inner Join with the WHERE Clause?
» Can Group Functions Be Mixed with Non-group Selection Fields?
» How To Use Subqueries in the FROM clause?
» How To Divide Query Output into Groups?
» How To Write an Inner Join with the WHERE Clause?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
Sun Jan 31, 2010 12:35 am by Micheal
» Accessing tables of other users in SQL
Sun Jan 31, 2010 12:34 am by Micheal
» Grouping commands in SQL
Sun Jan 31, 2010 12:32 am by Micheal
» Operations on Result Sets in SQL
Sun Jan 31, 2010 12:31 am by Micheal
» Subqueries in SQL
Sun Jan 31, 2010 12:30 am by Micheal
» Joining Relations in SQL
Sun Jan 31, 2010 12:28 am by Micheal
» Delete comand in SQL
Sun Jan 31, 2010 12:27 am by Micheal
» Update command in SQL
Sun Jan 31, 2010 12:26 am by Micheal
» Insert command in SQL
Sun Jan 31, 2010 12:23 am by Micheal