Discovering SQL: A Hands-On Guide for Beginners

AGGREGATE SQL FUNCTIONS REVISITED

SQL Standard includes a surprising number of advanced statistical functions (more than 20!) that you might never encounter solving day-to-day problems, and most enterprise-class RDBMSs added a few of their own. A detailed discussion of these SQL functions belongs in an advanced book. A list of the most common functions can be found in Table 5-1, later in this chapter.

Aggregate functions return a single value based on a specific calculation within a set (group) of values. In the most basic case, the group is the entire table data set.

AVG()

There is a line in a humorous essay by Stephen Leacock that describes a particular kind of library: “There are, of course, all the new books, the new fiction, because there is a standing order with Spentano to send up fifty pounds of new fiction by express once a week.” Supposing that we maintained our library in the same fashion, how would our accountant handle it?

SELECT 
         AVG(bk_price)       AS average_price
        ‚AVG (bk_page_count) AS average_pages
FROM books;

average_price     average_pages
----------------  --------------------
29.5758           597

This is how much we spent on books on average (depending on your data you might have spent more or less, on average, per book), and there are an average of 597 pages per book. Put in context, this data might even be useful to track your book spending habits, for instance.

Of course, depending on our needs, we may put conditions on our query to narrow down the data set for which the ...