Aggregate Functions

Aggregates are functions you can use to get summary values. You apply aggregates to sets of rows: to all the rows in a table, to just those rows specified by a WHERE clause, or to groups of rows set up in the GROUP BY clause (discussed in the next chapter). No matter how you structure the sets, you get a single value for each set of rows.

Take a look at the difference in the results of the following two queries: The first finds each individual yearly sale value in the titles table (one sale listing per row); the second calculates the total yearly sales for all books in the titles table (one total sale listing per set, and the table is the set).

SQL
select ytd_sales
from titles
  ytd_sales
===========
       4095
       4095
       3336
       4072
       3876
       2032
       4095
      15096
       8780
      18722
       2045
        111
      22246
        375
     (NULL)
       4095
        375
     (NULL)
[18 rows]

SQL
select sum(ytd_sales)
from titles
sum(titles.ytd_sales)
=====================
                97446
[1 row]

The first returns results for each qualified row in the table; the second summarizes all the qualified rows into one row.

Column headings for the aggregate column vary from system to system. You can use a display label to make the results easier to read:

SQL
select sum(ytd_sales) as Total
from titles
      Total
===========
      97446
[1 row]


The ANSI standard and most dialects of SQL do not allow you to mix row-by-row results and set results. The SELECT list must be pure, either all expressions (row values) or all aggregates (set values). The only exception is for grouping columns—columns on which you base groups when you use a GROUP BY clause. (GROUP BY and aggregates are covered in Chapter 6.)

SQL VARIANTS

Here's a query that's usually not allowed, because it mixes a row value (price) with a set value (sum(price)):

Adaptive Server Enterprise
select price, sum(price)
from titles

The problem is that price returns a value for each row, while sum(price) returns a value for each set (here, the table as a whole). Unless your system is designed to handle this kind of query, it will find the two results incompatible and will give you a syntax error.

To get summary and row results on most systems, you have to run two queries. To display the results simultaneously, use a report generator of some kind.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.225.55.151