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).
SQLselect 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]
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: SQLselect 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 VARIANTSHere's a query that's usually not allowed, because it mixes a row value (price) with a set value (sum(price)): Adaptive Server Enterpriseselect 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. |
18.225.55.151