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) ...