• Article

Collapsing Groups of Rows (Visual Database Tools)

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 (Visual Database Tools).

  • 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 (Visual Database Tools) and Sorting and Grouping Query Results (Visual Database Tools).

  • 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 (Visual Database Tools) and How to: Use HAVING and WHERE Clauses in the Same Query (Visual Database Tools).