Running SQL & Queries

Analyzing Data

Table of Content

Table of Content

Table of Content

Analysing your table

AQT offers a number of options to quickly analyse your table. These are available under the Analyse menu option, or Analyse toolbar button. It is possible to customize the toolbar to have individual buttons for each of these functions.

These all work by generating an SQL statement which makes the appropriate query.

Option

Function

Number of rows

Displays the number of rows in your table.

Number of distinct values of column

How many different values the column takes in the table. You must select at least one column first.

  • if the column is unique, the result will equal the number of rows in the table.

  • If however the column is the same for every row, the result will be 1.

This can often be a quick way to understand the characteristics of a column.

Analyse column

You must select at least one column first. This is a more useful version of the previous query. It will show you all the distinct values the column takes, and the number of times each value occurs. This provides a very simple way of quickly seeing the type of data held in a column.

Duplicate values of column

You must select at least one column first. This shows you which column values occur more than once in the table. This is often useful if you have a column which you think should be (mostly) unique – and you want to see which values are not unique.

SQL used by AQT

To see the SQL that AQT uses for these queries, look at the Status Bar of the Data Display window that shows the results. You can then edit this SQL by clicking on File > Edit SQL in SQL window.

Analysing Multiple Columns

The Number of distinct values of column, Analyse Column and Duplicate values of Column functions can be used be used with multiple columns from your table. Use the Ctrl and Shift keys to select multiple columns from your column list then click on the appropriate Analysis button.

You will not be able to do if you have selected Options > Unicode Options > Support unicode characters in table and column names. When this is selected, AQT uses a unicode-enabled listbox for the column for displaying column names. This listbox doesn't allow multiple items to be displayed.

Warning

  • Don’t try these options on very large tables or it can take a long time! Common sense should prevail.

Analyse data by grouping columns

When you are displaying your data in the Data Display window, you can use a function of the grid to automatically group your data by particular columns.

If you select View > Group-by Box (or Ctrl+G) AQT will display a Group-By box at the top of the display. This allows you to group your data by particular columns; this function is extremely useful for analysing your data.

  • If you drag a column to the group-by box, the data will be grouped by that column. You can group-by multiple columns.

  • You can expand or collapse sections of the group hierarchy by clicking on the + or – icons or double-clicking the item.

  • View > Collapse Rows / Expand Rows will collapse or expand the group hierarchy.

  • If you are grouping by multiple columns, Collapse/Expand will just collapse or expand the top column in the grouping. You can also use View > Collapse All or Expand All to collapse / expand all groups in the hierarchy.

  • You can expand or collapse the grouping by double-clicking the column in the Group-By box. Note that if you expand a column, but one of the higher-level column is collapsed, you will not see the expanded data. The columns at the higher levels will also need to be expanded.

  • You can removing the grouping by dragging the column and dropping it onto the grid (ignore the large X that you see when you do this).

Once you have grouped your data, you can calculate sub-totals on the data.