Skip to main content

Adding Aggregations

Aggregations allow you to summarize your data by calculating values across groups of rows. Visitran's No-Code UI provides an intuitive interface for adding aggregations without writing code.

Overview​

Aggregation operations combine multiple rows of data into summary statistics. This is essential for analysis, reporting, and understanding patterns in your data.

Available Aggregation Functions​

COUNT​

Count the number of rows in each group.

Use Case: Find the number of orders per customer, products per category, etc.

SUM​

Calculate the total of numeric values in each group.

Use Case: Total sales by region, total quantity ordered per product, etc.

AVERAGE (AVG)​

Calculate the arithmetic mean of numeric values in each group.

Use Case: Average order value, average rating per product, etc.

MIN​

Find the minimum value in each group.

Use Case: Earliest date, lowest price, minimum quantity, etc.

MAX​

Find the maximum value in each group.

Use Case: Latest date, highest price, maximum quantity, etc.

DISTINCT COUNT​

Count the number of unique values in each group.

Use Case: Number of unique customers per region, unique products sold, etc.

How to Add Aggregations in Visitran​

Step-by-Step Instructions​

  1. Select Your Model - Navigate to the model where you want to add aggregations
  2. Add Group By - Click "Add Group By" to define grouping columns
  3. Select Grouping Columns - Choose which columns to group by
  4. Add Aggregations - Select the aggregation functions and target columns
  5. Name Aggregated Columns - Provide meaningful names for your aggregated results
  6. Preview Results - View the aggregated data in the data grid
  7. Save - Apply the changes to your model

Grouping Columns​

Grouping columns define how your data is partitioned before aggregation:

  • Single Column - Group by one dimension (e.g., by customer)
  • Multiple Columns - Group by multiple dimensions (e.g., by customer and region)
  • Date Grouping - Group by time periods (day, month, quarter, year)

Common Use Cases​

Sales Summary by Region​

Group By: region
Aggregations:
  - COUNT(*) as order_count
  - SUM(amount) as total_sales
  - AVG(amount) as average_order_value

Customer Purchase Analysis​

Group By: customer_id
Aggregations:
  - COUNT(*) as total_orders
  - SUM(order_total) as lifetime_value
  - MAX(order_date) as last_order_date

Product Performance​

Group By: product_category, product_name
Aggregations:
  - COUNT(*) as units_sold
  - SUM(revenue) as total_revenue
  - AVG(rating) as average_rating

Time-Based Analysis​

Group By: order_year, order_month
Aggregations:
  - COUNT(DISTINCT customer_id) as unique_customers
  - SUM(order_amount) as monthly_revenue

Best Practices​

  1. Choose Meaningful Groups - Select grouping columns that align with your analysis goals
  2. Name Aggregations Clearly - Use descriptive names for aggregated columns
  3. Consider NULL Values - Understand how NULL values affect aggregations
  4. Validate Results - Check that aggregated totals match expected values
  5. Document Business Logic - Add comments explaining aggregation choices

Performance Considerations​

  • Index Grouping Columns - Ensure columns used in GROUP BY are indexed
  • Limit Groups - Too many distinct groups can impact performance
  • Pre-Filter Data - Apply filters before aggregation when possible
  • Aggregate Early - Perform aggregations as early as possible in your pipeline

Advanced Techniques​

Multiple Aggregations on Same Column​

Calculate several statistics for the same field:

SUM(amount) as total
AVG(amount) as average
MIN(amount) as minimum
MAX(amount) as maximum

Conditional Aggregations​

Use CASE statements within aggregations for conditional logic:

SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as completed_sales
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders

Rolling Aggregations​

Combine with window functions for running totals and moving averages.

Troubleshooting​

Unexpected NULL Results​

  • Check if source data contains NULL values
  • Use COALESCE to provide default values
  • Verify aggregation functions handle NULLs correctly

Performance Issues​

  • Reduce the number of distinct groups
  • Add indexes on grouping columns
  • Consider pre-aggregating in earlier stages

Incorrect Counts​

  • Verify you're using COUNT vs COUNT(DISTINCT) appropriately
  • Check for duplicate rows in source data
  • Review join operations that may create duplicates

For row-level calculations, see:

Detailed screenshots showing the aggregation interface will be added soon.