Page 76 - SQL

Use HAVING with Aggregate Functions

Unlike the WHERE clause, HAVING can be used with aggregate functions.

An aggregate function is a function where the values of multiple rows are grouped
together as input on certain criteria to form a single value of more significant meaning
or measurement (Wikipedia).

Common aggregate functions include COUNT(), SUM(), MIN(), and MAX().

This example uses the Car Table from the Example Databases.

SELECT CustomerId, COUNT(Id) AS [Number of Cars]
FROM Cars
GROUP BY CustomerId
HAVING COUNT(Id) > 1

This query will return the CustomerId and Number of Cars count of any customer who has more than
one car. In this case, the only customer who has more than one car is Customer #1.

The results will look like:

CustomerId Number of Cars

1 2

Use BETWEEN to Filter Results

The following examples use the Item Sales and Customers sample databases.

Note: The BETWEEN operator is inclusive.

Using the BETWEEN operator with Numbers:

SELECT * From ItemSales
WHERE Quantity BETWEEN 10 AND 17

This query will return all ItemSales records that have a quantity that is greater or equal to 10 and
less than or equal to 17. The results will look like:

Id SaleDate ItemId Quantity Price

1 2013-07-01 100 10 34.5

4 2013-07-23 100 15 34.5

5 2013-07-24 145 10 34.5

https://riptutorial.com/ 58