13. Filtering Results

WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set.

Where

SELECT*FROM Customers

WHERECustomerID=1;

GROUP BY

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SELECTcolumn_name,aggregate_function(column_name)

FROMtable_name

WHEREcolumn_name operator value

GROUPBYcolumn_name;

§ Example

SELECTShippers.ShipperName,COUNT(Orders.OrderID)ASNumberOfOrdersFROM Orders

LEFTJOIN Shippers

ONOrders.ShipperID=Shippers.ShipperID

GROUPBYShipperName;

Having

§ The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values.

§ The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECTcolumn_name,aggregate_function(column_name)

FROMtable_name

WHEREcolumn_name operator value

GROUPBYcolumn_name

HAVINGaggregate_function(column_name) operator value;

§ Example

SELECTEmployees.LastName,COUNT(Orders.OrderID)ASNumberOfOrdersFROM (Orders

INNERJOIN Employees

ONOrders.EmployeeID=Employees.EmployeeID)

GROUPBYLastName

HAVINGCOUNT(Orders.OrderID)> 10;

<<Prev                                                                                                        Next>>