Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Tips for sorting, grouping and sumarrizing SQL data

VIEWS: 7 PAGES: 5

tips and more tips on technology

More Info
  • pg 1
									10 tips for sorting, grouping, and summarizing SQL data
By Susan Sales Harkins

Version 1.0 November 6, 2006

Arranging data in a manner that’s meaningful can be a challenge. Sometimes all you need is a simple sort. Often, you need more—you need groups you can analyze and summarize. Fortunately, SQL offers a number of clauses and operators for sorting, grouping, and summarizing. The following tips will help you discern when to sort, when to group, and when and how to summarize. For detailed information on each clause and operator, see Books Online.

1

Bring order with a sort

More often than not, all your data really needs is a little order. SQL’s ORDER BY clause organizes data in alphabetic or numeric order. Consequently, similar values sort together in what appear to be groups. However, the apparent groups are a result of the sort; they aren’t true groups. ORDER BY displays each record whereas a group may represent multiple records.

2

Reduce similar values into a group

The biggest difference between sorting and grouping is this: Sorted data displays all the records (within the confines of any limiting criteria) and grouped data doesn’t. The GROUP BY clause reduces similar values into one record. For instance, a GROUP BY clause can return a unique list of ZIP codes from a source that repeats those values: SELECT ZIP FROM Customers GROUP BY ZIP Include only those columns that define the group in both the GROUP BY and SELECT column lists. In other words, the SELECT list must match the GROUP BY list, with one exception: The SELECT list can include aggregate functions. (GROUP BY doesn’t allow aggregate functions.) Keep in mind that GROUP BY won’t sort the resulting groups. To arrange groups alphabetically or numerically, add an ORDER BY clause (# 1). In addition, you can’t refer to an aliased field in the GROUP BY clause. Group columns must be in the underlying data, but they don’t have to appear in the results.

3

Limit data before it’s grouped

You can limit the data that GROUP BY groups by adding a WHERE clause. For instance, the following statement returns a unique list of ZIP codes for just Kentucky customers: SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ZIP It’s important to remember that WHERE filters data before the GROUP BY clause evaluates it. Like GROUP BY, WHERE doesn’t support aggregate functions.

Page 1
Copyright ©2006 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

10 tips for sorting, grouping, and summarizing SQL data

4

Return all groups

When you use WHERE to filter data, the resulting groups display only those records you specify. Data that fits the group’s definition but does not meet the clause’s conditions won’t make it to a group. Include ALL when you want to include all data, regardless of the WHERE condition. For instance, adding ALL to the previous statement returns all of the ZIP groups, not just those in Kentucky: SELECT ZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP As is, the two clauses are in conflict, and you probably wouldn’t use ALL in this way. ALL comes in handy when you use an aggregate to evaluate a column. For example, the following statement counts the number of customers in each Kentucky ZIP, while also displaying other ZIP values: SELECT ZIP, Count(ZIP) AS KYCustomersByZIP FROM Customers WHERE State = 'KY' GROUP BY ALL ZIP The resulting groups comprise all ZIP values in the underlying data. However, the aggregate column (KYCustomersByZIP) would display 0 for any group other than a Kentucky ZIP. Remote queries don’t support GROUP BY ALL.

5

Limit data after it’s grouped

The WHERE clause (# 3) evaluates data before the GROUP BY clause does. When you want to limit data after it’s grouped, use HAVING. Often, the result will be the same whether you use WHERE or HAVING, but it’s important to remember that the clauses are not interchangeable. Here’s a good guideline to follow when you’re in doubt: Use WHERE to filter records; use HAVING to filter groups. Usually, you’ll use HAVING to evaluate a group using an aggregate. For instance, the following statement returns a unique list of ZIP codes, but the list might not include every ZIP code in the underlying data source: SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1 Only those groups with just one customer make it to the results.

Page 2
Copyright ©2006 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

10 tips for sorting, grouping, and summarizing SQL data

6

Get a closer look at WHERE and HAVING

If you’re still confused about when to use WHERE and when to use HAVING, apply the following guidelines: WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records. HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

7

Summarize grouped values with aggregates

Grouping data can help you analyze your data, but sometimes you’ll need a bit more information than just the groups themselves. You can add an aggregate function to summarize grouped data. For instance, the following statement displays a subtotal for each order: SELECT OrderID, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY OrderID As with any other group, the SELECT and GROUP BY lists must match. Including an aggregate in the SELECT clause is the only exception to this rule.

8

Summarize the aggregate

You can further summarize data by displaying a subtotal for each group. SQL’s ROLLUP operator displays an extra record, a subtotal, for each group. That record is the result of evaluating all the records within each group using an aggregate function. The following statement totals the OrderTotal column for each group: SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP The ROLLUP row for a group with two OrderTotal values of 20 and 25 would display an OrderTotal of 45. The first record in a ROLLUP result is unique because it evaluates all of the group records. That value is a grand total for the entire recordset. ROLLUP doesn’t support DISTINCT in aggregate functions or the GROUP BY ALL clause.

Page 3
Copyright ©2006 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

10 tips for sorting, grouping, and summarizing SQL data

9

Summarize each column

The CUBE operator goes a step further than ROLLUP by returning totals for each value in each group. The results are similar to ROLLUP, but CUBE includes an additional record for each column in the group. The following statement displays a subtotal for each group and an additional total for each customer: SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE CUBE gives the most comprehensive summarization. It not only does the work of both the aggregate and ROLLUP, but also evaluates the other columns that define the group. In other words, CUBE summarizes every possible column combination. CUBE doesn’t support GROUP BY ALL.

10

Bring order to summaries

When the results of a CUBE are confusing (and they usually are), add the GROUPING function as follows: SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE The results include two additional values for each row: The value 1 indicates that the value to the left is a summary value—the result of the ROLLUP or CUBE operator. The value 0 indicates that the value to the left is a detail record produced by the original GROUP BY clause.

Page 4
Copyright ©2006 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html

10 tips for sorting, grouping, and summarizing SQL data

Additional resources
TechRepublic's Downloads RSS Feed Sign up for TechRepublic's Downloads Weekly Update newsletter Sign up for our SQL Server newsletter Check out all of TechRepublic's free newsletters "Optimize SQL Server without rewriting queries" (TechRepublic download) "Master the basic building blocks of an SQL database query" (TechRepublic download) "TechRepublic SQL Server QuickStart" (TechRepublic download)

Version history
Version: 1.0 Published: November 6, 2006

Tell us what you think
TechRepublic downloads are designed to help you get your job done as painlessly and effectively as possible. Because we're continually looking for ways to improve the usefulness of these tools, we need your feedback. Please take a minute to drop us a line and tell us how well this download worked for you and offer your suggestions for improvement. Thanks! —The TechRepublic Downloads Team

Page 5
Copyright ©2006 CNET Networks, Inc. All rights reserved. For more downloads and a free TechRepublic membership, please visit http://techrepublic.com.com/2001-6240-0.html


								
To top