Recipe7.4.Counting Rows in a Table


Recipe 7.4. Counting Rows in a Table

Problem

You 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.

Solution

When 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 

Discussion

When 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 Also

Appendix A for a refresher on GROUP BY functionality.




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net