Summarizing your Data

To help you summarize your data, you can use the Build Summary Query window. This can be invoked from:

• the Database Explorer window, Table > Summarize Table
• the Run SQL window, clicking on the Summary toolbar button

The Query Builder window provides a more comprehensive capability for building Summary Queries. The Build Summary Query window is designed for quickly building simple summary queries.

The Build Summary Query window is as follows:

The section Summary or Group-by queries gives a good overview of Summary queries. To recap, to summarize your table you need to specify:

• the columns you are summarizing-by
• the summary statistics. These will involve a summary function on a column

Specifying Summarize-By Columns

These are specified in the top pane:

• select a Column then click on Add. The column will be added to the box on the right
• you can select a Function if you wish to apply a function to the summarize-column. For instance, if your table just has a Year-column, and you want to summarize by Month, you can specify a function of MONTH (depending on whether your database has a function such as this.....).
• if you specify a function you may also which to specify Name. This the name by which this column is known.
• you can have multiple Summarize-by columns.
• you can manipulate the entries in the box on the right with Delete, Up and Down.

Specify the Summary Statistics

These are specified in the bottom pane:

• select a Column, select a Function then click on Add. The column/function will be added to the box on the right
• a Function must be specified. This gives the manner in which the column is summarized. Only summary ("aggregate") functions can be specified here (sum, avg, min, max etc).
• it is recommended that you specify a Name. This the name by which the summarized value will be displayed.
• you can have multiple Summary Statistic columns.
• a table-column can appear in more than one Summary Statistic. For instance, you can specify both min(SALARY) and max(SALARY).
• you can manipulate the entries in the box on the right with Delete, Up and Down.

Copy SQL

• You can copy the generated summary query to the Run SQL window with Copy to SQL window
• You can copy the generated summary query to the clipboard with Copy SQL to Clipboard

Running the Summary Query

• clicking on Run will display the results in the Data Display window (except for the next point)
• if you have invoked Build Summary Query from the Run SQL window, and the Run SQL window is displaying results, the results will be shown on the Run SQL window.

Charting the Summary Query

Clicking on Chart will invoke the Chart window. The query will be run and results charted.

• the Summary Statistics will become the Data Columns
• at least one of your Summary Statistics values must be numeric
• the (first) Summarize-by column will be the Label Column.
• if you have multiple Summarize-by columns, the chart may not produce meaningful results. If you have (say) two summarize-by columns, the resultant summary is best represented by a three-dimensional chart. AQT doesn't support three dimensional charts such as this.
• once you chart your summary query, the chart-window is linked to this window. As you amend your summary query and click on Chart, the chart is re-displayed. You do not get a separate Chart window every time you click on Chart.

Summary Type

Depending on how you invoke the Build Summary Query window, you will get different things for Summary Type.

• if you are summarizing a table, you will get just one option Build Summary Query
• if you are summarizing a query, you will get two options Build Summary Query and Summarize the Results of your Query. These relate to two different forms of syntax used for building the summary query.
  • Build Summary Query will create a summary query from your query
  • Summarize the Results of your Query will keep your query intact as a sub-select. It will then summarize the sub-select.

  For simple queries, the first of these two options is the most sensible. However this will fail if you have a complex query, such as a multi-table join. In this case the second option is the best option.

• if you have a summary query in the Run SQL window, and click on Summary, you will get two options:
  • Modify your Summary Query. Use this if you wish to amend your summary query.
  • Summarize the results of your Summary Query. This gives you the ability to build a summary of your summary query. Sometimes it is useful to do a summary of a summary and this options allows you to do this.