Search these areas:
Support Forums
Product Info

DataWeb: What's New
Creating an Application
The DataWeb Designer
Application Management
Data Tables
Data Tables
Data Analysis
-Adding Totals to Your View
-Aggregating View Data
-Using Aggregation Functions in Script
Object Model
Full-Text Search
Domain and Email Hosting
HotBlock Modules
Account Management
New Responsive UI Module

DataWeb Help
Support Forums
Script Library
Knowledge Base

Home > Support

DataWeb User's Guide

Aggregating View Data
A powerful way to analyze data in a view is to group common data together. You can group data using an aggregation function.

For example, suppose you have a Tools table that lists tools, including the type of tool and its price. Your table includes several tools of each type -- power tools, hand tools, specialty tools, and so on. Suppose that you would like to see the number of tools belonging to each type, and the average price for a particular type of tool. You can group your data by tool type and quickly find the data that you're looking for.

First, create a new view on the Tools table. Add the ToolType field, which is a lookup to another table containing tool types. Also add the Price field. For now, don't add any other fields.

Select the data region of your view, and set the Query Type property to Grouped. Your view will automatically update to show your data grouped on ToolType, with one record for each type of tool. If you select the ToolType field, you'll see that it has a property called Aggregate Function, and that this property is set to Group By. This property setting specifies that the data in the view should be grouped by tool type.

You'll also see the Price field showing the sum of all tool prices for each type of tool. If you select the Price field, you'll see that its Aggregate Function property is set to Sum. You can change this setting to Average to show the average price for each type of tool.

Now all that remains is to count the number of tools in each category. Add another field from the Tools table to the view, such as the ToolName field. When you add this field, you'll see that your data is no longer grouped -- you now have one row for each unique tool name, rather than one row for each tool type. If you select the ToolName field, you'll see that the Aggregate Function property is set to Grouped, meaning that the view is grouping data by the ToolName field. If you change the Aggregate Function property for this field to Count, your data will once again be grouped by the ToolType field, and the ToolName field will show the number of tools of each type.

Using aggregation functions to display data the way you want can be tricky. Try to start by adding the smallest number of fields possible to a new view. If your data is not being grouped as you expect, check the setting of the Aggregate Function property for each field. Make sure that only the fields on which you want to group are set to Group By.

Affiliate | Partner | Terms of Use | Privacy Policy | Contact Us | Pricing | Bring DataWeb In-House    
DataWeb, 720 North 10th Street, A #145, Renton, Washington 98057 *425-583-5970* Fax 484-770-4706* Email Us