The Language of SQL- P24:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | Grouping Data 101 The third format of the COUNT function allows you to use the DISTINCT keyword in addition to a column name. Here s an example SELECT COUNT DISTINCT FeeType AS Number of Fee Types FROM Fees This statement is counting the number of distinct values for the FeeType column. The result is Number of Fee Types 3 This means that there are three different values found in the FeeType column. Grouping Data The previous examples of aggregation functions are interesting but of somewhat limited value. The real power of the aggregation functions will become evident after we introduce the concept of grouping data. The GROUP BY keyword is used to separate data returned from a SELECT statement into any number of groups. For example when looking at the previous Grades table you may be interested in analyzing test scores based on the grade type. In other words you want to separate the data into two separate groups quizzes and homework. The value of the GradeType column can be used to determine which group each row belongs to. Once data has been separated into groups then aggregation functions can be utilized so that summary statistics for each of the groups can be calculated and compared. Let s proceed with an example that introduces the GROUP BY keyword SELECT GradeType AS Grade Type AVG Grade AS Average Grade FROM Grades GROUP BY GradeType ORDER BY GradeType 102 Chapter 10 Summarizing Data The result is Grade Type Average Grade Homework 86 Quiz 77 In this example the GROUP BY keyword specifies that groups are to be created based on the value of the GradeType column. The two columns in the SELECT columnlist are GradeType and a calculated field that uses the AVG function. The GradeType column was included in the columnlist because when creating a group it s usually a good idea to include the column on which the groups are based. The Average Grade calculated field aggregates values based on all rows in each group. Notice that the average homework grade has been .
