This query fetches the following result.
|
Type |
Tot_Num |
|---|---|
|
—– |
——- |
|
GSM |
8 |
|
CDMA |
8 |
|
3G |
4 |
The primary rule for using the GROUP BY clause is to make sure all the columns in your select list are present in your GROUP BY clause.
SELECT Type, Make, COUNT(Type) AS Tot_Num
FROM Mobiles_phones
GROUP BY Type, Make
This query gives you the following result.
|
Type |
Model |
Tot_Num |
|---|---|---|
|
—- |
—– |
——- |
|
GSM |
Nokia |
3 |
|
CDMA |
Nokia |
2 |
|
3G |
Nokia |
2 |
Suppose you wanted to list all the mobile phone types that have a minimum of four entries. You might think of using a WHERE clause, but remember that you should not use aggregate functions in the WHERE clause. Instead use the HAVING clause.
SELECT Type, COUNT (Type) AS Tot_Num
FROM Mobiles_phones GROUP BY Type
HAVING COUNT (Type) > 4
This query will give you the following result.
|
Type |
Tot_Num |
|---|---|
|
—- |
——- |
|
GSM |
8 |
|
CDMA |
8 |
Essential Aggregate Functions
Aggregate functions are acceptable as expressions in the following:
- The select list of a SELECT statement.
- A COMPUTE or COMPUTE BY clause.
- A HAVING clause.
Transact-SQL has seven key aggregate functions. We will use the table below to explain the basic uses of these functions.
|
Name |
Age |
Salary |
|---|---|---|
|
Alex |
34 |
11000 |
|
Bob |
23 |
20000 |
|
Cathy |
22 |
12500 |
|
Dona |
19 |
35000 |
|
James |
21 |
16000 |
|
Kelly |
45 |
21550 |
|
Mosses |
35 |
20600 |
|
Nancy |
21 |
16000 |
|
Peter |
23 |
16000 |
|
Rosalie |
32 |
10000 |
|
Samuel |
45 |
11000 |
1. MAX (column)
The MAX function returns the highest value of a selected column. It does not include NULL values in the calculation.
Syntax:
SELECT MAX (column) FROM Table
Example:
SELECT MAX (salary) FROM Employee
Result: 35000.
2. MIN (column)
The MIN function returns the lowest value of a selected column. It does not include NULL values in the calculation.
Syntax:
SELECT MIN (column) FROM Table
Example:
SELECT MIN (salary) FROM Employee
Result: 11000.