Example 1 demonstrates the use of the Sum and Count functions. Sum and Count are two examples of a class of functions called aggregate functions.
An aggregate function is a function that operates on a range of values in a column. The aggregate functions are:
|
Avg |
Large |
Mode |
Sum |
|
Count |
Last |
Percent |
Var |
|
CumulativePercent |
Max |
Small |
VarP |
|
CumulativeSum |
Median |
StDev |
|
|
First |
Min |
StDevP |
|
About crosstab functions
Although the crosstab functions (CrosstabAvg, CrosstabAvgDec, CrosstabCount, CrosstabMax, CrosstabMaxDec, CrosstabMin, CrosstabMinDec, CrosstabSum, and CrosstabSumDec) behave like aggregate functions, they are not included on the list because they are for crosstabs only and are designed to work in the crosstab matrix.
A few restrictions apply to the use of aggregate functions. You cannot use an aggregate function:
-
In a filter
-
In a validation rule
-
As an argument for another aggregate function
This example demonstrates the use of the Sum aggregate function.
What you want to do
Using the employee table in the PB Demo DB as the data source, you create a DataWindow object using at least the Emp_id and the Status columns. You want the DataWindow object to display the number of active employees, terminated employees, and on leave employees in the company.
How to do it
In the summary band in the workspace, add three computed fields to the DataWindow object that use the Sum and If functions:
Sum(If(status = "A", 1, 0))
counts the number of Active employees in your company;
Sum(If(status = "T", 1, 0))
counts the number of Terminated employees in your company;
Sum(If(status = "L",1,0))
counts the number of On Leave employees in your company.
By clicking the Page computed field button, you can also add a Page computed field in the footer band to display the page number and total pages at the bottom of each page of the DataWindow object.
What you get
Here is what the design of the DataWindow object looks like.
Here is the last page of the DataWindow object, with the total number of active employees, terminated employees, and on leave employees in the company displayed.
If you want more information
What if you decide that you also want to know the number of active employees, terminated employees, and on leave employees in each department in the company?
To display the active employees, terminated employees, and on leave employees in each department:
-
Select Design>Data Source from the menu bar so that you can edit the data source.
-
Select Design>Select tables from the menu bar and open the Department table in the Select painter workspace, which currently displays the Employee table with the Emp_id and Status columns selected.
-
Select the department_dept_name column to add it to your data source.
-
Select Rows>Create Group from the menu bar to create a group and group by department name.
-
In the trailer group band, add three additional computed fields:
Sum(If(status = "A", 1, 0) for group 1)
counts the number of active employees in each department;
Sum(If(status = "T", 1, 0) for group 1)
counts the number of terminated employees in each department;
Sum(If(status = "L", 1, 0) for group 1)
counts the number of on leave employees in each department.
Here is what the design of the grouped DataWindow object looks like.
Here is the last page of the DataWindow object with the number of active employees, terminated employees, and on leave employees in the shipping department displayed, followed by the total number of active employees, terminated employees, and on leave employees in the company.
