Database Toolbox

Group By

Use the Group by function to organize query results by field in the order you specify. The Group by results contain only unique occurrences (no rows that have identical data). You can also use the Having function to further restrict the Group by results.

After selecting from Fields in the Visual Query Builder dialog box, click Group by in Advanced query options. The Group By Clauses dialog box appears.

Group By Clauses Dialog Box

1. Select an entry from the Fields list box.
2. To specify the grouping order, type the Group key number for that field. For example, 1 means group by that field first, 2 means group by that field second, etc.
3. Click Apply to create the clause.

1. The group-by clause appears in the Current clauses area.

4. Repeat steps 1 through 3 for all fields that you selected from the Fields list box in the Visual Query Builder dialog box. If you do not specify a value for the Group key number, it will have the highest number when added to the Current clauses area. Clauses appear in ascending order by group key number.
5. To change a clause, select it in the Current clauses area and click Edit. Make changes for that clause and click Apply.

1. To remove a clause, select the clause and then click Delete. Use Ctrl+click or Shift+click to select multiple clauses to delete.

   Changes automatically update the Group key number for the other clauses.

6. Click OK.

1. The Group By Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box reflects the group-by clause.

7. To further restrict the group-by results, use Having.

Example Using Group By Clauses

This example retrieves sales volumes for January and February, organizing the results first for January sales, and then for February sales:

1. In the Visual Query Builder dialog box, select the fields January and February from the salesVolume table.
2. In Advanced query options, click Group by.

1. The Group By Clauses dialog box appears.

3. For January, specify the Group key number value to be 1 and click Apply. For February, specify the Group key number value to be 2 and click Apply (shown in example below).

   

4. Click OK.

1. The Group By Clauses dialog box closes. The SQL statement in the Visual Query Builder dialog box reflects the group-by clause conditions you specified.

5. Assign a MATLAB workspace variable and execute the query. From the results, you can see that records with the same value for January sales (column 1), 3000, are grouped together.

   

6. Compare these results to a query which does not specify group-by criteria (shown below as B). There are three records in B for which January sales are 3000. These are not grouped together. In addition, for two of them, the February sales (column 2) are the same, 2400. By comparison, there are only two records in A where January sales are 3000, because using Group by includes only unique occurrences in the results.

   

See Also

• Order By Clauses Dialog Box
• Advanced Query Options
• Visual Query Builder Dialog Box

Subquery

Having

© 1994-2005 The MathWorks, Inc.