SQL Unit 5 Aggregation, GROUP BY, and HAVING. Kirk Scott. 5.1 Grouping By One Field 5.2 Grouping By More than One Field 5.3 GROUP BY with HAVING 5.4 More on Nulls. 5.1 Grouping By One Field. 1. Recall that the term aggregation referred to built-in functions like these:
1. Recall that the term aggregation referred to built-in functions like these:
2. Remember also that the records in the Carsale table include the spno, and it is possible to write a query that orders the results of a query by that field:
3. What if you would like the subtotals of the sums of the salespricesfor the cars sold by each salesperson?
This query will give the subtotal for each spno in the Carsale table.
4. Here is another example, using COUNT, where the function is applied to * rather than to a single field in the table.
Recall that the meaning of COUNT(*) is to count all of the records where any of the fields are non-null.
5. It\'s not necessary to include the GROUP BY field in the query results.
On the other hand, there are limitations on what fields can be included in the results of a GROUP BY query.
It would not be possible to show the multiple custno\'s belonging to a single spno, so this is not allowed.
The bottom line is that in a GROUP BY query, the SELECT can include at most the GROUP BY field and the field that the aggregate is calculated on.
6. It is possible to use GROUP BY and ORDER BY together in a single query.
1. It is also possible to GROUP BY more than one field at a time in a query.
This query will give the sum of the stickerprices for every possible combination of make and model.
The results would also include rows for the three cases where either the make, model, or both fields were null in the original records in the Car table.
2. It is again useful to compare the GROUP BY query with the analogous ORDER BY query:
In this query the primary sort key is make and the secondary sort key is model.
3. Observe that it would also be possible to write queries where the order that the fields are selected is changed.
The second example would put the make column first and the model column second, but the sort order has been changed to sort first by model and than by make.
4. It bears repeating that including a GROUP BY field in the SELECT is optional.
It also bears repeating that it is not possible to include in the SELECT any fields except for the aggregate field and the fields in the GROUP BY.
5. It is always possible to specify an order for the results of a query in addition to doing GROUP BY.
1. In a simple query, a WHERE clause causes the SELECT to pick out only certain sets of records in a table based on a condition on the value of an individual field.
In a query with GROUP BY, the HAVING clause can be used to achieve similar results as the WHERE clause in a simple query.
For example, this query will show the spno\'s and the sums of the salesprices of cars that they sold, but only for those salespeople who sold a total of at least 50000 dollars worth of cars overall:
Here is another straightforward example which will find the salespeople and the counts of the numbers of cars they sold, if they sold more than 4 cars:
2. For better or worse, the HAVING clause can also be applied to the GROUP BY field or fields.
There is nothing wrong with the previous example, but the following alternative may be preferable.
3. It is possible to have both a condition on a GROUP BY field (a non-aggregate field) and the aggregate field in a query.
So, for example, this query will find the makes and the sums of their stickerprices for makes that appear after Chevrolet in alphabetical order, and whose stickerprice sums are greater than or equal to 50000.
4. All of the examples so far have concentrated on conditions on the group by fields or the aggregate.
This last example dispenses with HAVING and where clauses except for the joining condition in order to clearly illustrate doing a join and GROUP BY together.
The Salesperson table is the table in the example database which includes nulls.
The following query will return an average calculated by dividing by 4 rather than 5:
If you want to make sure that nulls are included, you have to use the NZ function.
2. The thing to remember is that GROUP BY will include a group for null values even though the aggregate functions ignore nulls.
GROUP BY also returns a row for the case where bossno equals 333, which happens to be a group where 4 records in the Salesperson table have that value.