Problem solve Get help with specific problems with your technologies, process and projects.

Row sums and the "totals" row

My company is using Oracle and I'd like to make a report, as below:

a          b          sum 
1          10         11 
2          20         22 
total_a    total_b    total_all

What's the SQL command to get the "sum"?

The answer is pretty simple, at least as far as getting the sum of a + b on each row --

select a
     , b
     , (a + b) as sum
  from yourTable

Obtaining the "totals" row is a bit trickier. In the example, this would be total_a, total_b, and total_all.

You could use Oracle's proprietary SQL extension called PL/SQL (PL = procedural language) to produce the column totals. Sybase and Microsoft SQL/Server have a similar language extension called Transact-SQL. Both PL/SQL and Transact-SQL provide mechanisms whereby a "totals" row is calculated as a by-product of the report.

If you do not have a procedural language available to do the totals row, you may be interested in the following solution which involves generating the totals row through simple SQL --

   select "detail"
        , a
        , b
        , (a + b) as sum
     from yourTable
union all
   select "total"
        , sum(a)
        , sum(b)
        , sum(a + b) 
     from yourTable
 order by 1
        , 2

I'd like to make several comments about this query, which may help you with future queries of this nature.

The query consists of two subqueries, and the results of these subqueries are UNIONed. The first subquery obtains all the detail rows, while the second subquery obtains the single totals row. The second subquery is a good example of aggregate functions being used without a GROUP BY clause -- when there is no GROUP BY clause, the entire table forms the group, and the result is one row.

So in the combined result set from the two subqueries, the first column is used to distinguish detail rows from the totals row. This column is used in the ORDER BY clause, and the values that I chose, the strings "detail" for detail rows and "total" for the totals row, were completely arbitrary, except for the single fact that as it happens, alphabetically, "detail" precedes "total" so the totals row comes at the end. (Neat, eh?) I usually use the values 1 and 2 (because for one thing, they're shorter -- don't forget, they're on each row) except in this case the values 1 and 2 would easily be confused with the ordinal numbers 1 and 2 in the ORDER BY clause, which refer to the first and second columns of the result set of the UNION.

Within the block of detail rows, rows are sequenced by values of a, the second column. The total row is in a block of rows by itself, and this block is sequenced too, but since there's only one total, this doesn't really matter.

UNION ALL is specified instead of UNION, because we know there will never be any duplicate rows between the first and second subqueries, since the first column is perforce different. UNION would invoke a sort in order to detect duplicate rows, so we use UNION ALL to make the query more efficient.

For More Information

  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL