Recipe7.3.Summing the Values in a Column

Recipe 7.3. Summing the Values in a Column

Problem

You want to compute the sum of all values, such as all employee salaries, in a column.

Solution

When computing a sum where the whole table is the group or window, simply apply the SUM function to the columns you are interested in without using the GROUP BY clause:

  1 select sum(sal) 2  from emp   SUM(SAL) ----------      29025

When creating multiple groups or windows of data, use the SUM function with the GROUP BY clause. The following example sums employee salaries by department:

  1 select deptno, sum(sal) as total_for_dept 2   from emp 3  group by deptno     DEPTNO  TOTAL_FOR_DEPT ----------  --------------         10            8750         20           10875         30            9400

Discussion

When searching for the sum of all salaries for each department, you are creating groups or "windows" of data. Each employee's salary is added together to produce a total for his respective department. This is an example of aggregation in SQL because detailed information, such as each individual employee's salary, is not the focus; the focus is the end result for each department. It is important to note that the SUM function will ignore NULLs, but you can have NULL groups, which can be seen here. DEPTNO 10 does not have any employees who earn a commission, thus grouping by DEPTNO 10 while attempting to SUM the values in COMM will result in a group with a NULL value returned by SUM:

  select deptno, comm   from emp  where deptno in (10,30)  order by 1     DEPTNO       COMM ---------- ----------         10         10         10         30        300         30        500         30         30          0         30       1300         30  select sum(comm)   from emp  SUM(COMM) ----------       2100 select deptno, sum(comm)   from emp  where deptno in (10,30)  group by deptno     DEPTNO   SUM(COMM) ----------  ----------         10         30        2100