Database Tour Documentation
Data Aggregation Functions

What is Data Aggregation

In databases, data aggregation is a process of gathering some statistical information from database tables. It could be, for example, statistics of sales, requests etc. which are stored in your database.

Basic aggregate function are:

• Sum
• Minimum value
• Maximum value
• Average value
• Count

In Database Tour, you can work with data aggregates by SQL or application interface means.

Data Aggregation Using SQL

Using SQL is the recommended way to perform the data aggregation, especially for large datasets.

To create a new SQL window in active database, click New SQL Window button , choose Query | New... menu, or press Ctrl+Q. Then, type your SQL query in the SQL editor and execute the query. Here is an example of the data aggregation query:

SELECT 
  SUM(payment_amount) AS payment_sum,
  COUNT(payment_id) AS payment_count,  
  AVG(payment_amount) AS avg_payment_amount,  
  MIN(payment_amount) AS min_payment_amount,  
  MAX(payment_amount) AS max_payment_amount,  
  MIN(payment_date) AS min_payment_date,  
  MAX(payment_date) AS max_payment_date
FROM payments

Please note, that usually you use SUM and AVG functions on numeric columns only, and MIN and MAX functions on numeric and date compatible columns. Some databases have special aggregate functions for text columns (see LISTAGG function in Oracle or STRING_AGG function in PostgreSQL and SQL Server). Read SQL tutorials and your database documentation to get more info about how to write correct data aggregation SQL queries.

Data Aggregation Using Interface

If you do not have enough knowledge of SQL or do not want to type SQL text, you can use Database Tour GUI:

• Open your source dataset, i.e. select a table from the table list or execute a SQL query.
• If you chose the table, switch to Data page.
• Double-click the corresponding column data or choose Tools | Arithmetic Functions... menu.

Summarizing Selected Cells in Data Grids

You can also select (by mouse) a range of cells in the visible part of the database grid, and see their aggregation results in the status bar below.