O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

ELIMINATING DUPLICATE DATA

The full syntax for the aggregate functions discussed in this chapter follows the same pattern:

FUNCTION (DISTINCT | ALL)

Here's an example:

AVG(DISTINCT | ALL)

The default is ALL, meaning that all values in the set will serve as input to the function. Using the DISTINCT keyword, the duplicate values will be ignored. For instance, we can count all the books published by Wiley:

SELECT 
    COUNT(bk_publisher) AS pub_count
FROM books
WHERE bk_publisher = ‘Wiley’;

pub_count 
------------------
4

The result is 4, which is how many Wiley books are in our library. The results will be quite different if we ask for a DISTINCT record count:

SELECT COUNT(DISTINCT bk_publisher)  AS pub_count
FROM books
WHERE bk_publisher = ‘Wiley’;

pub_count 
----------------
1

The four values have collapsed into one because we have exactly one occurrence of the value in the data set. Of course, both variants could be used within the same query:

SELECT 
  COUNT(bk_publisher)             AS pub_count
‚ COUNT(DISTINCT bk_publisher)    AS distinct_count
FROM books;

pub_count   distinct_count
----------- ---------------
12          9

The preceding results show 9 distinct publishers in our table, out of a total of 12.

It matters where you insert your DISTINCT keyword. Used within an aggregate function, it produces aggregated results for the set of distinct values; used outside it makes the return result distinct. Consider these two queries and their respective outputs:

 SELECT DISTINCT COUNT(bk_publisher) ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required