Collapsing Groups of Rows

You can create a query result in which each result row corresponds to an entire group of rows from the original data. When collapsing rows, there are several things to keep in mind:

• You can eliminate duplicate rows   Some queries can create result sets in which multiple identical rows appear. For example, you can create a result set in which each row contains the city and state name of a city containing an author – but if a city contains several authors, there will be several identical rows. The resulting SQL might look like this:

  SELECT city, state
  FROM authors
  

  The result set generated by the preceding query is not very useful. If a city contains four authors, the result set will include four identical rows. Since the result set does not include any columns other than city and state, there is no way to distinguish the identical rows from each other. One way to avoid such duplicate rows is to include additional columns that can make the rows different. For example, if you include author name, each row will be different (provided no two like-named authors live within any one city). The resulting SQL might look like this:

  SELECT city, state, fname, minit, lname
  FROM authors
  

  Of course, the preceding query eliminates the symptom, but does not really solve the problem. That is, the result set has no duplicates, but it is no longer a result set about cities. To eliminate duplicates in the original result set, and still have each row describe a city, you can create a query returning only distinct rows. The resulting SQL might look like this:

  SELECT DISTINCT city, state
  FROM authors
  

  For details about eliminating duplicates, see How to: Exclude Duplicate Rows.

• You can calculate on groups of rows   That is, you can summarize information in groups of rows. For example, you can create a result set in which each row contains the city and state name of a city containing an author, plus a count of the number of authors contained in that city. The resulting SQL might look like this:

  SELECT city, state, COUNT(*)
  FROM authors
  GROUP BY city, state
  

  For details about calculating on groups of rows, see Summarizing Query Results and Sorting and Grouping Query Results.

• You can use selection criteria to include groups of rows   For example, you can create a result set in which each row contains the city and state name of a city containing several authors, plus a count of the number of authors contained in that city. The resulting SQL might look like this:

  SELECT city, state, COUNT(*)
  FROM authors
  GROUP BY city, state
  HAVING COUNT(*) > 1
  

  For details about applying selection criteria on groups of rows, see How to: Specify Conditions for Groups and How to: Use HAVING and WHERE Clauses in the Same Query.

See Also

Other Resources

Specifying Search Criteria

Designing Queries and Views