910 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

Now you’ll specify that we want the sum the quantities. Right-click the Quantity field in the Grid pane and select the Group By option from the context menu. A new column will be inserted after the Sort Order column. This column is set automatically to Group By for all the fields.

Now select the Group By cell of the Quantity row, expand the drop-down list, and select the Sum option. You have just specified that the field Quantity must be summed. The Group By option tells the Query Builder to group together all the rows that refer to the same product. This ensures that the sum will include all the products, because the rows of the Order Details table that refer to the same product are grouped together).

Notice that the Alias cell of the Quantity row has become Expr1 (it’s no longer a column, but an aggregate). Set the alias to Total Items. Something has changed in the Diagram pane too (see Figure 20.16). The summation symbol has appeared next to the Quantity field (even though this field isn’t selected to appear in the output of the query), and the grouping symbol has appeared next to the ProductName field.

Figure 20.16

A query with totals

Run the query now and see the results in the lower pane. Each product name appears only once, and the number next to it is the total number of items sold.

If you close the Query Builder window now, you’ll be prompted as to whether you want to save the new view and to specify a name for it. The definition will be saved to the Northwind database, along with the other objects of the database.

SQL at Work: Counting Rows

Let’s say you want to find out the number of orders in which each product appears. Go back to the Server Explorer and open the previous view (or the Query Analyzer). Add the Orders table, which will be automatically related to the Order Details table with the OrderID field. Click the OrderID

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE QUERY BUILDER 911

field in the Orders table. A new line will be added to the Grid pane, and its Group By column will be set automatically to Group By. Set it to Count Distinct and its alias to “# Of Orders.” We’re going to sum the orders in which each product appears. The Count Distinct aggregate function is similar to the Count function, but it will not include the same order twice (if the same product appears in two rows of the same order). Run the query. This time you’ll get one line per product. The Alice Mutton item has been ordered 37 times, and the total items sold are 978.

Alice Mutton	978	37
Aniseed Syrup	328	12
Boston Crab Meat	1103	41
Camembert Pierrot	1577	51

The SELECT statement generated by the SQL Builder is the following. Notice that the Orders table isn’t involved in the query. All the information we need resides in the Order Details table. The Products table is included so that we can display product names instead of product IDs.

SELECT	TOP 100 PERCENT dbo.Products.ProductName,
	SUM(dbo.[Order Details].Quantity) AS [Total Items],
	SUM(dbo.[Order Details].OrderID) AS [# Of Orders]
FROM	dbo.Products
	INNER JOIN dbo.[Order Details] ON
	dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY	dbo.Products.ProductName
ORDER BY	dbo.Products.ProductName

The phrase TOP 100 PERCENT tells SQL Server to return all qualifying rows and is optional. The Query Builder inserted it so that you can change the value and limit the number of selected rows. Change the default aliases of the two calculated columns and execute the query again by clicking the button with the exclamation mark.

Limiting the Selection

So far, we’ve extracted data from all rows. Practically, we never work with all the rows in the data- base—we select a subset based on chronological, geographical, or other criteria. In this section we’ll modify the previous query so that it retrieves the totals over a time period. As you can guess, we’ll use the WHERE clause to limit the selected rows.

Our selection will be chronological. We’ll sum the items sold in a year (or any other interval you wish). This will introduce a little additional complexity to our query, because the information on which the selection will be based doesn’t appear in the Order Details table. The date of each order is stored in the Orders table, so we must add this table to our query.

Select the OrderDate field to the Grid pane. We want to specify two criteria for the date: it must be after the starting date and before the ending date. So, add the Orders.OrderDate field twice. To add a second instance of the same field, expand the first empty cell in the left column and, from the drop-down list, select its name. Then move to the Group By column of the row of the OrderDate field. Change its value to WHERE and, in the Criteria column, enter the following:

>= ‘1/1/1998’

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

912 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

In the second instance of the same field, expand the Group By column, set it to WHERE, and then enter the following string in the Criteria column:

<= ‘1/1/1999’

The Query Builder has generated the following SQL statement:

SELECT	TOP 100 PERCENT dbo.Products.ProductName,
	SUM(dbo.[Order Details].Quantity) AS [Total Items],
	SUM(dbo.[Order Details].OrderID) AS [# Of Orders]
FROM	dbo.Products
	INNER JOIN dbo.[Order Details] ON
	dbo.Products.ProductID = dbo.[Order Details].ProductID
	INNER JOIN dbo.Orders ON
	dbo.[Order Details].OrderID = dbo.Orders.OrderID
WHERE	(dbo.Orders.OrderDate >=
	CONVERT(DATETIME, ‘1998-01-01 00:00:00’, 102))
	AND (dbo.Orders.OrderDate <=
	CONVERT(DATETIME, ‘1999-01-01 00:00:00’, 102))

GROUP BY dbo.Products.ProductName

ORDER BY dbo.Products.ProductName

As you can see, the Query Builder inserted the appropriate statements to convert your values to dates. You can make this statement a little more compact by using the BETWEEN operator. Remove the cells corresponding to the OrderDate field from the first column. Then add the OrderDate field again, set its Group By column to WHERE, and in the Criteria columns, enter the expression BETWEEN ‘1/1/1998’ AND ‘1/1/1999’. When no time is specified, it’s assumed that it’s the first second of the specified date. The date 1/1/1998 includes the first day of the year. The date 12/31/1998 doesn’t include the last day of the year, because it will be converted to 1998-12-31 00:00:00. If you specify the first and last day of the year, the totals will be calculated over a period of 364 days, not 365 days (assuming the year is not leap). So, you must either specify the following date, or add a time part to the date to take into consideration the 24 hours of the final day: 12/31/1998 23:59:59.

Execute the query and you will see the following lines at the top of the Results pane:

Alice Mutton	217	11
Aniseed Syrup	108	4

Go back to the Grid pane and change the dates to calculate the same results for the year 1997. The two criteria should be:

>= ‘1/1/1997’ <= ‘1/1/1998’

Execute the new query and you will see the same product names, only with different totals. Here’s the revised query’s code:

SELECT TOP 100 PERCENT dbo.Products.ProductName,

SUM(dbo.[Order Details].Quantity) AS [Total Items],

SUM(dbo.[Order Details].OrderID) AS [# Of Orders]

FROM dbo.Products

INNER JOIN dbo.[Order Details] ON

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

THE QUERY BUILDER 913

dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN dbo.Orders ON

dbo.[Order Details].OrderID = dbo.Orders.OrderID WHERE (dbo.Orders.OrderDate BETWEEN

CONVERT(DATETIME, ‘1997-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘1998-1-1 00:00:00’, 102))

GROUP BY dbo.Products.ProductName ORDER BY dbo.Products.ProductName

Parameterized Queries

How about running the same query with different dates? Let’s modify our query once again, and make the two dates parameters of the queries. Each time you’ll be executing the new query, you’ll be prompted to specify the starting and ending dates.

Replace the two dates in the Criteria column of the Grid pane with a question mark. The revised expression should now read:

Between ? And ?

If you run the query, you’ll get an error message telling you that parameters aren’t supported for this type of query. We’re designing a view; that’s why you can’t use parameters. Click OK to get rid of the message, and you’ll be prompted to enter the values of the two parameters (Figure 20.17). A question mark in a query corresponds to a parameter, and you must supply the values for the parameters in the order in which they appear in the query. Enter the two dates in Define Query Parameters window and you’ll see its output in the Results pane.

Figure 20.17

Specifying the parameters for a query

In the following section, we’ll convert this statement to a stored procedure, and you’ll see how you can pass values for the query’s parameters. Because the behavior of the query depends on the values of its parameters, this is a parameterized query.

Calculated Columns

Let’s add yet another step of complexity to our query. We’ll modify our query so that it calculates the total revenues generated by each product. Move down in the Field column of the Grid pane, and in the first free cell, enter the following expression:

Quantity * UnitPrice * (1 – Discount)

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com