SQL VISUAL QUICKSTART GUIDE- P21:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 6 Aggregating Distinct Values with DISTINCT To calculate the average of a set of distinct values Type AVG DISTINCT expr expr is a column name literal or numeric expression. The result s data type is at least as precise as the most precise data type used in expr. To count distinct non-null rows Type COUNT DISTINCT expr expr is a column name literal or expression. The result is an integer greater than or equal to zero. The queries in Listing return the count sum and average of book prices. The non-DISTINCT and DISTINCT results in Figure differ because the DISTINCT results eliminate the duplicates of prices and from calculations. Tips The ratio COUNT DISTINCT COUNT tells you how repetitive a set of values is. A ratio of one or close to it means that the set contains many unique values. The closer the ratio is to zero the more repeats the set has. DISTINCT in a SELECT clause and DISTINCT in an aggregate function don t return the same result. The three queries in Listing count the author IDs in the table title_authors. Figure shows the results. The first query counts all the author IDs in the table. The second query returns the same result as the first query because COUNT already has done its work and returned a value in a single row before DISTINCT is applied. In the third query DISTINCT is applied to the author IDs before COUNT starts counting. Listing Some DISTINCT aggregate queries. See Figure for the results. Listing SELECT COUNT AS COUNT FROM titles SELECT COUNT price AS COUNT price SUM price AS SUM price AVG price AS AVG price FROM titles SELECT COUNT DISTINCT price AS COUNT DISTINCT SUM DISTINCT price AS SUM DISTINCT AVG DISTINCT price AS AVG DISTINCT FROM titles COUNT 13 COUNT price SUM price AVG price ------------------------------- 12 COUNT DISTINCT SUM DISTINCT AVG DISTINCT --------------- ----------- ------------ 10 Figure Results of Listing . 180 Summarizing and Grouping Data
