Recipe 7.4. Counting Rows in a TableProblemYou want to count the number of rows in a table, or you wish to count the number of values in a column. For example, you want to find the total number of employees as well as the number of employees in each department. SolutionWhen counting rows where the whole table is the group or window, simply use the COUNT function along with the "*" character: 1 select count(*) 2 from emp COUNT(*) ---------- 14 When creating multiple groups, or windows of data, use the COUNT function with the GROUP BY clause: 1 select deptno, count(*) 2 from emp 3 group by deptno DEPTNO COUNT(*) ---------- ---------- 10 3 20 5 30 6 DiscussionWhen counting the number of employees for each department, you are creating groups or "windows" of data. Each employee found increments the count by one to produce a total for her respective department. This is an example of aggregation in SQL because detailed information, such as each individual employee's salary or job, is not the focus; the focus is the end result for each department. It is important to note that the COUNT function will ignore NULLs when passed a column name as an argument, but will include NULLs when passed the "*" character or any constant; consider: select deptno, comm from emp DEPTNO COMM ---------- ---------- 20 30 300 30 500 20 30 1300 30 10 20 10 30 0 20 30 20 10 select count(*), count(deptno), count(comm), count('hello') from emp COUNT(*) COUNT(DEPTNO) COUNT(COMM) COUNT('HELLO') ---------- ------------- ----------- -------------- 14 14 4 14 select deptno, count(*), count(comm), count('hello') from emp group by deptno DEPTNO COUNT(*) COUNT(COMM) COUNT('HELLO') ---------- ---------- ----------- -------------- 10 3 0 3 20 5 0 5 30 6 4 6 If all rows are null for the column passed to COUNT or if the table is empty, COUNT will return zero. It should also be noted that, even if nothing other than aggregate functions are specified in the SELECT clause, you can still group by other columns in the table; for example: select count(*) from emp group by deptno COUNT(*) ---------- 3 5 6 Notice that you are still grouping by DEPTNO even though it is not in the SELECT clause. Including the column you are grouping by in the SELECT clause often improves readability, but is not mandatory. If you do include it (in the SELECT list), it is mandatory that is it listed in the GROUP BY clause. See AlsoAppendix A for a refresher on GROUP BY functionality. |