Grouping Results

In the previous examples of aggregate functions (COUNT, MAX, MIN, SUM) we have seen that the outputs of these functions are summarised into a single row of result table. There is an instance that we want summary of these functions to be grouped according to a specified column. This grouping summary can be done by using GROUP BY clause. Example 15 illustrates the use of GROUP BY clause.

Note that only column names that appear in the GROUP BY clause can be in the SELECT list unless the names is used for the aggregate functions.

Example 15: Use of GROUP BY

Query 15: Find the number of product supplied by each supplier.

SELECT SuppNo, COUNT(ProductNo) AS NumOfProduct FROM Product

Group By SuppNo;

This statement finds the number of product using aggregate function COUNT for each Supplier based on the SuppNo. The result table is shown in Table 3.15.

Table 3.15: Result Table for Example 15 SuppNo NumOfProduct

S8843 2 S9888 1 S9898 1 S9995 1 Restricting grouping (HAVING Clause)

Similar to the WHERE clause, which allows us to filter rows, we can use HAVING clause to filter the groups specify in the GROUP BY clause. Therefore, when you want to use HAVING, GROUP BY clause must be applied too.

Typically, the aggregate function is used in the condition for the HAVING clause.

Example 16: Use of HAVING

Query 16: Find the OrderNo that has more than one product SELECT OrderNo, Count(ProductNo) AS NumOfProduct FROM Delivery

GROUP BY OrderNo

HAVING COUNT(ProductNo) > 1;

This operation groups the Delivery data based on OrderNo and lists only those group that has more than one product. The output for this operation is shown in Table 3.16.

Table 3.16: Result table for Example 16 OrderNo NumOfProduct

1120 3 4399 2

3.3.5 Subqueries

In this section we are going to learn how to use subqueries. Here we provide examples of subqueries that involve the use of SELECT statement within another SELECT statement which is also sometimes referred to as nested SELECT. In terms of the order of the execution, the inner SELECT will be performed first and the result from the inner SELECT will be used for the filter condition in the outer SELECT statement. Example 17 and 18 illustrate the use of subqueries.

Example 17: Using a subquery with equality

Query 17: List the product names and its price per unit for products that are supplied by ABX Technics

SELECT Names AS ProductNames, UnitPrice FROM Product

WHERE SuppNO = { SELECT SuppNo

FROM Supplier

WHERE Name = „ABX Technics‰};

First the inner SELECT statement is executed to get the supplier number of ABX Technics. The output from this statement is tested as part of the search condition in the WHERE clause of the outer SELECT statement. Note that the Â=Ê sign is used in the WHERE clause of the outer SELECT since the result from the inner SELECT contains only one value.

The final result table from this query is shown in Table 3.17.

Table 3.17: Result table for Example 17 ProductNames UnitPrice 17 inch Monitor 200 19 inch Monitor 250

Example 18: Nested subquery: use of IN

List the supplier number, product names and its price per unit for products that are supplied by supplier from Petaling Jaya.

SELECT SuppNo AS SupplierNo, Names AS ProductName, UnitPrice FROM Product

WHERE SuppNO IN { SELECT SuppNo FROM Supplier

WHERE City = „Petaling Jaya‰};

In this query it is possible to have the output from the inner SELECT statement with more than one value. Therefore, the ÂINÊ keyword is used in the search condition with for the WHERE clause in the outer SELECT statement. The result table for the above statement is shown in Table 3.18.

Table 3.18: Result Table for Example 18

SupplierNo ProductNames UnitPrice S9898 Color Laser Printer 200

S9990 Color Scanner 250

S9995

Multitable Queries

So far weÊve learnt how to retrieve data using SELECT statement from only one table. Sometimes we need results that contain columns from more than one table. Thus, we need to perform a join operation to combine these columns into one result table. To perform a join, we need to specify the tables to be used in the FROM clause. The join condition that specifies the matching or common column/s of the tables to be joined is written in the WHERE clause. Examples 19, 20 and 21 illustrate how to join tables.

Example 19: Simple join

Query 19: List the supplier names for each product.

SELECT p.Name AS ProductName, s.Names AS SupplierName FROM Product p, Supplier s

WHERE s.SuppNo = p.SuppNo;

This statement joins two tables which are Product and Supplier. Since the common column for both tables is SuppNo, therefore this column is used for the join condition in the WHERE clause. The output for this simple join statement is shown in Table 3.19.

Table 3.19: Result Table for Example 19 ProductName SupplierName 17 inch Monitor ABX Technics 19 inch Monitor ABX Technics Laser Printer SoftSystem Color Laser Printer ID Computers Color Scanner ITN Suppliers

Example 20: Sorting a join

Query 20: Sort the list of products based on supplier name and for each supplier name sort the list based on Product names in descending order.

SELECT Product.Name AS ProductName, Supplier.Names AS SupplierName FROM Product p, Supplier s

WHERE s.SuppNo = p.SuppNo ORDER BY s.Name, p.Name DESC;

This statement is similar to the previous example, except it includes the ORDER BY clause for sorting purposes. The result is sorted in ascending order by supplier name and for those supplier that have more than one product the product name is sorted in descending order.

Table 3.20: Result Table for Example 20 ProductName SupplierName 19 inch Monitor ABX Technics 17 inch Monitor ABX Technics Laser Printer SoftSystem Color Laser Printer ID Computers

Color Scanner ITN Suppliers Example 21: Three table join

Query 21: Find the supplier names of the product that are delivered in Jan 2007.

Sort the list based on Supplier name.

SELECT Supplier.Names AS SupplierName, Product.Name AS ProductName, DeliveryDate

FROM Supplier s, Product p, Delivery d

WHERE s.SuppNo = p.SuppNo AND p.ProductNo = d.ProductNo AND (DeliveryDate >= „1-Jan-07‰ and DeliveryDate <= „31-Jan-07‰)

ORDER BY s.Name;

This queries require to join three tables. All the join conditions are listed in the WHERE clause. As noted earlier, the common column names for both tables to be joined need to be used as the join condition. To join the supplier and product, the supplier number is used and to join the product and delivery tables, the product number is used. The result from this join is shown in the Table 3.21.

Table 3.21: Result Table for Example 21

SupplierName ProductName DeliveryDate ABX Technics 17 inch Monitor 27-Jan-2007 ITN Suppliers Color Scanner 28-Jan-2007

SoftSystem Laser Printer 27-Jan-2007