Discovering SQL: A Hands-On Guide for Beginners

A GLIMPSE OF AGGREGATE FUNCTIONS

Once you put numeric values into your database there is quite a lot you can do with them besides displaying them in reports. Wouldn't it be nice to find out how many books you have in your library? Or how much you've spent on them? What is the most or least expensive book you've ever bought? The SQL aggregate functions are there to help you. Table 4-5 lists some of the most commonly used aggregate functions.

Let's start with counting the books. The syntax is deceptively simple.

SELECT COUNT(*) AS total_count FROM books;
total_count
-----------
12

What we get is the row count in the BOOKS table; assuming that we have no duplicate copies in the table, we ought to be all right. The people who designed SQL were pretty smart and foresaw this situation, hence the DISTINCT keyword we can use to weed off duplicates. Just run this query and we'll be in business:

SELECT DISTINCT COUNT(*) FROM books; From Boris: Output?

Somehow, results are exactly the same, even though we know we have some duplicates. If you take a closer look at the query you'll see that we apply DISTINCT keyword to the results produced by function COUNT(), which, being but a single number, will be distinct by definition. We need to make sure that we are COUNTing DISTINCT records, but with this syntax we cannot use * for all fields, we have to spell actual fields to perform the count on:

SELECT COUNT(*) AS total_records, COUNT( DISTINCT bk_title) AS distinct_titles FROM books; total_records ...