Database Reference
In-Depth Information
Figure 6.46
Use built-in function COUNT.
Figure 6.47
Use built-in function with GROUP BY.
However, mixing columns and functions in a SELECT clause will not generate an error if a
GROUP BY clause is used. GROUP BY puts the rows with the same data value from a column
in a group and the function will return a value for each group. In such a case, the correct output
will be displayed. Figure 6.47 shows a query that selects the capacity and counts the number of
classroom ids that have the same capacity. he GROUP BY clause includes the Capacity column
since the grouping is based on classroom capacity.
As indicated in Figure 6.47, three classrooms have the capacity of 20, two classrooms have the
capacity of 25, two classrooms have the capacity of 30, and one classroom has the capacity of 40.
Notice that column used by the GROUP BY clause should be the same column in the SELECT clause.
When using the GROUP BY clause, you cannot use the WHERE clause to specify the search
condition. Instead, you should use the HAVING clause. HAVING selects only certain groups out
of all the groups speciied by the GROUP BY clause. As an example, let us use a search condition
that returns the irst names and last names of students who are enrolled in more than one class
(see Figure 6.48).
Figure 6.48 shows the students who are enrolled in more than one class.
In the above, we have discussed some commonly used built-in aggregate functions. Next, you
will learn how to use scalar functions.
6.5.2 Scalar Functions
A scalar function returns a single value. he aggregate functions discussed earlier are a type of
scalar functions. he commonly used scalar functions are GETDATE, DATEDIFF, ROUND,
