Example

This example creates a query that shows all of the cities that employees live in, and summarizes employee information into a column containing the count of employees living in each city. There will be one row returned for each city, regardless of how many employees live in that city.

1. Open a new query and select the table of interest. In this case, open the EMPLOYEE table. Add it to the query and then click the Done button.
2. Click the columns you want displayed in the query. Do not include the columns that contain data that you want to summarize yet. For this example, add the CITY and STATE columns.
3. 
   
   Click the Create Computed Column button on the toolbar (or the Create Computed Column command from the Column menu) to display the Create Computed Column dialog box.
4. From the Aggregate Function drop-down list, double-click Count.
5. From the Table/Column drop-down list, double-click E_NO.

   It is a good idea, when generating a count, to choose a column that uniquely identifies the records that you want to count. You could also use an asterisk, which indicates that you want a count of all the records. The column name should be positioned inside the parentheses that appear after the count function.

6. A column name is provided for the column, but you may want to change it to something more meaningful. Click the Column Name field and type in EmployeeCount.
   Note:

   Column names must satisfy the rules for a long identifier, discussed in the SQL Reference. That is, they may be up to 18 characters long and must begin with a letter. They cannot contain spaces.

7. Click OK to accept the Computed Column.
8. Now examine the Query Conditions Grid. You will notice that a new column called EMPLOYEECOUNT has been added at the end of the column list. The Order/Group row has also been added to the grid. You first saw this row when you defined a sort order for a query. Now it contains some new icons. The
   
   symbol indicates that the column is a computed column. If you click this symbol, the Modify Computed Column dialog box for this column is displayed so that you can modify the column definition.
9. In addition, a
   
   symbol appears in the STATE and CITY columns. Whenever an aggregate function appears in a query, all other columns must be grouped. SQLWizard does this for you automatically. Click the
   
   symbol.

   The Group By dialog box is displayed. Notice that there are two lists. The UnGrouped Columns includes the computed column EMPLOYEECOUNT, which SQLWizard will not allow you to group. The Grouped Columns include the CITY and STATE columns, which SQLWizard will not allow you to ungroup unless you delete the EMPLOYEECOUNT column.

10. The order in which columns appear in the GROUP BY clause is important. SQLWizard places the columns in the list in the order in which they appear on the Query Conditions Grid. If this is not the correct order for your query, you should rearrange them. To do so, click a column to select it, and then use the Up and Down buttons to reposition the column. When positioning the columns, keep in mind that a column that contains a subset of the rows of another column should appear later in the list. In this example, the CITY column should appear after the STATE column, because there are a number of cities in each state. Move the STATE column to the top of the list.
11. Once you have ordered the columns correctly, click OK to close the Group By dialog box. Notice the number that appears to the right of the GROUP symbol shows that STATE is the first grouped column, and CITY is the second grouped column.
12. 
    
    You have now created a query containing summary rows. Use the SQL button on the toolbar to examine the SELECT statement generated for this command. Notice the COUNT function and the GROUP BY clause.
13. 
    
    Use the Query Design button on the toolbar to return to the Query Design window.
14. Save the query if you would like to keep it.
15. 
    
    Click the Run button to run the SQL command. When the result appears you will see a row for every city in the EMPLOYEE table. The number of EMPLOYEE records that have a matching CITY value will appear in the EMPLOYEECOUNT column.
16. Exit from the Result window