GROUP BY Clause - Microsoft SQL Server Black Book

Now let’s group our data in the Authors table so that our authors are listed in alphabetical order, grouped by state. First we’ll need to add the state column to the SELECT list and list the other displayed columns in the GROUP BY clause. See Listing 5.8 for this grouped query and the corresponding output. Note that there is no ORDER BY in this list but that the names returned are in alphabetical order. The GROUP BY will sort in the order we wish to group columns.

Listing 5.8 Phone list grouped by state.

/* Query 1 Phone list grouped by state */

SELECT 'Name' = UPPER(SUBSTRING(au_fname,1,1)) + '. ' + SUBSTRING(au_lname,1, 15),

'State' = state,

'Phone' = '(' + SUBSTRING(phone,1,3) + ') ' + SUBSTRING(phone,5,8)

FROM authors

GROUP BY state, au_lname, au_fname, phone Output

Name State Phone

--- --- --- A. Bennet CA (415) 658-9932 C. Carson CA (415) 548-7723 A. Dull CA (415) 836-7128 M. Green CA (415) 986-7020 B. Gringlesby CA (707) 938-6445 S. Hunter CA (415) 836-7128 L. Karsen CA (415) 534-9219 C. Locksley CA (415) 585-4620 S. MacFeather CA (415) 354-7128 H. McBadden CA (707) 448-4982 M. O'Leary CA (408) 286-2428 D. Straight CA (415) 834-2919 D. Stringer CA (415) 843-2991 J. White CA (408) 496-7223 A. Yokomoto CA (415) 935-4228 M. DeFrance IN (219) 547-9982 M. Smith KS (913) 843-0462 S. Panteley MD (301) 946-8853 I. del Castillo MI (615) 996-8275 R. Blotchet-Halls OR (503) 745-6402 M. Greene TN (615) 297-2723 A. Ringer UT (801) 826-0752 A. Ringer UT (801) 826-0752 (23 row(s) affected) http://www.itknowledge.com/reference/standard/1576101495/ch05/149-151.html (3 of 4) [1/27/2000 6:16:06 PM]

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles. Microsoft SQL Server Black Book

(Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

GROUP BY can be used for much more than I have illustrated here. Calculating summary data and reports with sectional summaries and grand totals can come into play as well. See Listing 5.9 for a sales grouping query from the Sales table. This query uses GROUP BY and HAVING clauses, which are frequently used together.

I also use the aggregate functions SUM() and MAX() for the first time in Query 2 to add each sale together into one record in the result set and to display the last date of a sale for that title. We’ll cover aggregate functions later in the chapter. For more information on GROUP BY statements and HAVING clauses, see Microsoft SQL Server Books Online.

Listing 5.9 Sales report query.

/* Query 1 Listing of sales with no grouping */ SELECT title_id,

ord_date, qty

FROM sales

ORDER BY title_id

/* Query 2 Volume sales > 20 */ SELECT 'Title ID' = title_id,

'Last Sale Date' = MAX(ord_date), 'Total Sales' = SUM(qty)

FROM sales GROUP BY title_id HAVING SUM(qty) > 20 http://www.itknowledge.com/reference/standard/1576101495/ch05/151-154.html (1 of 3) [1/27/2000 6:16:08 PM] Go! Keyword --- Go!

ORDER BY SUM(qty) DESC Output Query 1

title_id ord_date qty --- --- --- BU1032 Sep 14 1994 12:00AM 5

BU1032 Sep 14 1994 12:00AM 10 BU1111 Mar 11 1993 12:00AM 25 BU2075 Feb 21 1993 12:00AM 35 BU7832 Oct 28 1993 12:00AM 15 MC2222 Dec 12 1993 12:00AM 10 MC3021 Sep 14 1994 12:00AM 25 MC3021 Sep 14 1994 12:00AM 15 PC1035 May 22 1993 12:00AM 30 PC8888 May 24 1993 12:00AM 50 PS1372 May 29 1993 12:00AM 20 PS2091 Sep 13 1994 12:00AM 3 PS2091 Sep 13 1994 12:00AM 75 PS2091 Sep 14 1994 12:00AM 10 PS2091 Sep 14 1994 12:00AM 20 PS2106 May 29 1993 12:00AM 25 PS3333 May 29 1993 12:00AM 15 PS7777 May 29 1993 12:00AM 25 TC3218 Jun 15 1992 12:00AM 40 TC4203 Jun 15 1992 12:00AM 20 TC7777 Jun 15 1992 12:00AM 20 (21 row(s) affected) Output Query 2

Title ID Last Sale Date Total Sales --- --- --- PS2091 Sep 14 1994 12:00AM 108

PC8888 May 24 1993 12:00AM 50 MC3021 Sep 14 1994 12:00AM 40 TC3218 Jun 15 1992 12:00AM 40 BU2075 Feb 21 1993 12:00AM 35 PC1035 May 22 1993 12:00AM 30 BU1111 Mar 11 1993 12:00AM 25 PS2106 May 29 1993 12:00AM 25 PS7777 May 29 1993 12:00AM 25 (9 row(s) affected)

Some special conditions are associated with the GROUP BY and HAVING clauses:

• ANSI-SQL requires that every column (nonaggregate) in the SELECT list be mentioned in your GROUP BY statement.

• Columns in a HAVING clause must have one and only one value. This requirement precludes subqueries that return more than one row.

• Any query with a HAVING clause must contain a GROUP BY clause. These requirements are usually not hard to meet and should pose little problem to you as a programmer.

Microsoft SQL Server has added Transact-SQL extensions: GROUP BY statements can include expressions, and GROUP BY ALL displays all groups, even those that would be filtered out by the WHERE clause restrictions.

Join Conditions

Now we’ll need to put some information together from more than one table so that the title ID can show the name and author for each sales report line in our new report. We’ll use the same basic query from above and add a join

condition to it so that the server connects the data. See Listing 5.10 for the new query and the sample output.

Microsoft SQL Server supports ANSI join syntax and Transact-SQL syntax (Microsoft SQL Server extended SQL). Use of inner, cross, and outer joins is supported fully on Microsoft SQL Server. The syntax for ANSI joins is slightly different than for Transact-SQL, so a trip to Microsoft SQL Server Books Online would be prudent. Transact-SQL uses join operators instead of actual text to perform the join in a WHERE clause. In the following examples, Transact-SQL methods are used.

Query 1 in Listing 5.10 displays what is in the Titleauthor table. This data is a collection of key values used to join together related pieces of information. Not all databases need this type of table, but good relational database design will produce a similar table in most situations.

Query 2 joins the Sales, Titleauthor, and Author tables. You specify the tables in a comma-delimited list in the FROM clause. In the SELECT list, you use the table.column reference to specify each column independently of the others. This allows you to use data from many tables and present it to the user as if it were from one table.

Hiding the complexity of the database structure is very important to your users. If a column name is unique to all tables involved in a query, such as qty in the Sales table, you can list the column without the table reference. The Optimizer will not choke on it because it knows there is only one qty column in all three tables.

Previous Table of Contents Next

Brief Full Advanced

Search Search Tips

To access the contents, click the chapter and section titles.

Microsoft SQL Server Black Book (Publisher: The Coriolis Group)

Author(s): Patrick Dalton ISBN: 1576101495 Publication Date: 07/01/97

Search this book:

Previous Table of Contents Next

I use the table.column reference in all my join queries to keep myself in check and to track which column comes from which table.

Listing 5.10 Sales by author join example.

/* Query 1 titleauthor table */ SELECT *

FROM titleauthor ORDER by title_id

/* Query 2 Volume sales > 20 by author */

SELECT 'Author' = UPPER(SUBSTRING(authors.au_fname,1,1)) + '. ' + SUBSTRING(authors.au_lname,1, 15), 'Total Sales' = SUM(sales.qty)

FROM sales, titleauthor, authors

WHERE sales.title_id = titleauthor.title_id AND authors.au_id = titleauthor.au_id GROUP BY authors.au_fname, authors.au_lname HAVING SUM(sales.qty) > 20

ORDER BY SUM(qty) DESC

/* Query 3 Volume sales > 20 by author */

SELECT 'Author' = UPPER(SUBSTRING(a.au_fname,1,1)) + '. ' + SUBSTRING(a.au_lname,1, 15), 'Total Sales' = SUM(s.qty)

FROM sales s, titleauthor ta, authors a WHERE s.title_id = ta.title_id

AND a.au_id = ta.au_id

GROUP BY a.au_fname, a.au_lname

http://www.itknowledge.com/reference/standard/1576101495/ch05/154-158.html (1 of 4) [1/27/2000 6:16:09 PM]

Go!

Keyword

---

HAVING SUM(s.qty) > 20 ORDER BY SUM(s.qty) DESC Output Query 1

au_id title_id au_ord royaltyper --- --- --- --- 213-46-8915 BU1032 2 40 409-56-7008 BU1032 1 60 267-41-2394 BU1111 2 40 724-80-9391 BU1111 1 60 213-46-8915 BU2075 1 100 274-80-9391 BU7832 1 100 712-45-1867 MC2222 1 100 722-51-5454 MC3021 1 75 899-46-2035 MC3021 2 25 238-95-7766 PC1035 1 100 427-17-2319 PC8888 1 50 846-92-7186 PC8888 2 50 486-29-1786 PC9999 1 100 724-80-9391 PS1372 2 25 756-30-7391 PS1372 1 75 899-46-2035 PS2091 2 50 998-72-3567 PS2091 1 50 998-72-3567 PS2106 1 100 172-32-1176 PS3333 1 100 486-29-1786 PS7777 1 100 807-91-6654 TC3218 1 100 648-92-1872 TC4203 1 100 267-41-2394 TC7777 2 30 472-27-2349 TC7777 3 30 672-71-3249 TC7777 1 40 (25 row(s) affected) Output Query 2

Author Total Sales --- --- A. Ringer 148 A. Ringer 133 A. Dull 50 M. Green 50 S. Hunter 50 M. O'Leary 45 S. MacFeather 45 M. DeFrance 40 S. Panteley 40 C. Carson 30 C. Locksley 25 (11 row(s) affected)

Notice that in the WHERE clause of these queries I have had to place a join condition for each of the tables in my list. The Sales table has a title_id column that matches a title_id column in the

Titleauthor table. By saying that these two columns are equal, you allow the server to add the information in the Titleauthor table to the list of available columns you can choose from. The Titleauthor table has an au_id column that corresponds to the au_id column of the Authors table. A good rule of thumb is that for each table that you wish to join, there should be one join

condition in the WHERE clause.

NOTE: The formula N = T –1 should come to mind when joining tables. T is the number of

tables in your FROM clause and N is the number of join conditions you should have. The wrong number of join conditions can return incorrect result sets to the user.

Aliases

In Query 3 of Listing 5.10, I have introduced aliases to each of the table names in the FROM clause. I do not have to do this, but it prevents me from typing things over and over again. In addition, aliases enhance readability of the code.

Notice in Query 2 the long lines of code, with the table reference repeated many times.

Repeating the table name is not bad; it’s just costly in the amount of typing you must do. Queries 2 and 3 return the same results, yet Query 3 is about 55 characters shorter. This might seem insignificant, but when you have to pass queries over a network or modems many times during the running of an application, it can save time in the communications between client and server. (I do not typically pass a query string from client to server; instead, I use stored procedures, which keep the requests as short as possible. See Chapter 6 for more on stored procedures and their benefits.)

To use aliases, add a space after each of the tables in the FROM clause and the character or characters you wish to refer or connect to each table. The Query Optimizer checks for the alias in the FROM clause first; then it uses those aliases to parse the SELECT list and WHERE clauses, respectively.

In document Microsoft SQL Server Black Book (Page 136-143)