Aggregating and Grouping Data

Aggregation transforms many rows into fewer, more meaningful summary rows. Instead of looking at 10,000 individual transactions, you see monthly totals. Instead of every customer record, you see counts by segment. This guide covers the aggregation techniques you’ll use in almost every analysis.

What is Aggregation?

Aggregation answers “how much?” and “how many?” questions by combining multiple values into summaries.

Raw data	Aggregated
10,000 order rows	Total revenue: $1.2M
500 customers	150 Premium, 350 Standard
Daily sales for a year	Monthly averages

Without aggregation, you’re drowning in details. With it, you see the big picture.

A Working Example

Let’s use a simple orders table to illustrate aggregation concepts throughout this guide:

Order	Date	Region	Category	Customer	Amount	Units
1	Jan 15	East	Electronics	Acme Corp	$1,200	2
2	Jan 18	West	Clothing	Beta Inc	$350	5
3	Jan 22	East	Electronics	Acme Corp	$800	1
4	Feb 03	West	Electronics	Gamma LLC	$2,100	3
5	Feb 10	East	Clothing	Delta Co	$425	4
6	Feb 14	East	Electronics	Acme Corp	$950	2
7	Feb 20	West	Clothing	Beta Inc	$275	3
8	Mar 05	East	Clothing	Delta Co	$600	6
9	Mar 12	West	Electronics	Gamma LLC	$1,800	2
10	Mar 18	East	Electronics	Acme Corp	$1,500	3

This small dataset has multiple dimensions you can slice by (Region, Category, Customer, Month) and multiple values you can calculate on (Amount, Units). We’ll use it to show the difference between pivots and grouped aggregations.

Basic Aggregations

Totals (Sum)

Add up values across rows:

"What's the total revenue?"
"Sum of units sold"
"Total expenses for 2024"

From our example: Total Amount = $10,000, Total Units = 31

Counts

Count rows or distinct values:

"How many orders are there?"
"Count of unique customers"
"How many products have been sold?"

From our example: 10 orders, 4 unique customers

Averages

Calculate the mean value:

"What's the average order value?"
"Average customer age"
"Mean time to resolution"

From our example: Average order = $1,000

Other Statistics

"What's the median order value?"
"Min and max prices"
"Standard deviation of revenue"

Function	What it tells you
Sum	Total across all rows
Count	Number of rows
Count Distinct	Number of unique values
Average (Mean)	Typical value (affected by outliers)
Median	Middle value (robust to outliers)
Min / Max	Range of values
Standard Deviation	How spread out values are

Grouping vs Pivoting: Understanding the Difference

If you’ve used Excel pivot tables, you’re familiar with viewing one metric across two dimensions—like Revenue by Region and Quarter in a grid. That’s powerful, but it’s just the beginning.

Grouping in Querri lets you calculate many metrics at once for each combination of dimensions. This is where the real analytical power lives.

Pivot Tables: Two Dimensions, One Metric

A pivot table shows a single calculation spread across rows and columns:

"Pivot total revenue by Region and Month"

Region	January	February	March
East	$2,000	$1,375	$2,100
West	$350	$2,375	$1,800

This is great for spotting patterns—you can immediately see that West had a strong February while East was steadier across months.

Pivots are perfect for:

• Quick visual comparisons across two dimensions
• Heatmap-style analysis
• Period-over-period views (months as columns, categories as rows)
• Presentation-ready tables

Grouped Aggregations: One Dimension, Many Metrics

Here’s where Querri shines. Instead of one metric across a grid, calculate everything you need for each group:

"Group by Region and calculate total revenue, order count, average order value, total units, and units per order"

Region	Total Revenue	Orders	Avg Order	Total Units	Units/Order
East	$5,475	6	$913	18	3.0
West	$4,525	4	$1,131	13	3.3

Now you’re not just seeing revenue—you’re seeing the story. East has more orders but smaller average values. West has fewer, larger orders. This single table gives you five insights instead of one.

The Power of Multi-Metric Grouping

Most Excel users create separate pivot tables for each metric they need. In Querri, ask for everything at once:

"Group by Customer and show: total revenue, order count, average order value,
first order date, most recent order date, and days between first and last order"

One prompt, one table, complete customer intelligence.

Combined Dimensions with Many Metrics

You can group by multiple dimensions AND calculate multiple metrics:

"Group by Region and Category, then calculate total revenue, order count, average order value, and total units"

Region	Category	Revenue	Orders	Avg Order	Units
East	Electronics	$4,450	4	$1,113	8
East	Clothing	$1,025	2	$513	10
West	Electronics	$3,900	2	$1,950	5
West	Clothing	$625	2	$313	8

This tells you: Electronics drives revenue in both regions, but West’s electronics orders are much larger. Clothing has smaller order values but more units per order.

When to Use Each Approach

Use Case	Best Approach	Why
”Show me revenue by region and month”	Pivot	Clean grid for comparison
”Analyze customer behavior”	Grouped metrics	Need multiple KPIs per customer
”Compare Q1 vs Q2 by product”	Pivot	Two-way comparison
”Build a segment profile”	Grouped metrics	Many attributes per segment
”Quick visual pattern scan”	Pivot	Easy to spot outliers in grid
”Export data for further analysis”	Grouped metrics	More flexible downstream

Exploratory vs Final Analysis

Exploratory: Cast a Wide Net

When you’re exploring, ask for lots of metrics to see what’s interesting:

"Group by Customer and calculate everything useful: total revenue, order count,
average order, total units, first order date, last order date, days as customer,
number of categories purchased, favorite category"

You might discover that your best customers by revenue aren’t your most frequent buyers—or that certain customers only buy one category while others buy across your whole catalog.

Exploratory Power Prompt

"Group by [dimension] and calculate 10 useful metrics that would help
me understand the differences between groups"

Let Querri suggest metrics you might not have thought of.

Final Analysis: Focused and Clean

For reports and dashboards, narrow down to the metrics that matter:

"Group by Region and Category showing total revenue, order count, and average order value.
Sort by revenue descending."

Then export or add to a dashboard:

"Export this to Excel"
"Add this table to my Sales Dashboard"

Common Aggregation Patterns

Time-Based Summaries

See trends over time:

"Monthly revenue for 2024"
"Weekly order counts"
"Daily active users"
"Quarterly sales by region"

Choose the Right Time Grain

• Daily: For recent, detailed analysis (last 30 days)
• Weekly: Good balance of detail and readability
• Monthly: Standard for business reporting
• Quarterly: Executive summaries and trends

Category Breakdowns

Understand composition:

"Revenue by product line"
"Customers by segment"
"Orders by status"
"Sales by channel"

Ranking and Top N

Find the leaders and laggards:

"Top 10 customers by revenue"
"Bottom 5 products by sales"
"Top 20 cities by order count"

Period-Over-Period Comparison

Compare time periods:

"Compare this month's revenue to last month"
"Year-over-year growth by category"
"This quarter vs same quarter last year"

Creating Effective Pivot Tables

"Create a pivot table of sales by region and quarter"
"Pivot revenue by product category and month"
"Cross-tab orders by day of week and hour"

Example result:

Region	Q1	Q2	Q3	Q4
East	$120K	$135K	$128K	$142K
West	$98K	$110K	$105K	$118K
Central	$85K	$92K	$88K	$95K

Pivot tables are great for:

• Spotting patterns across two dimensions
• Heatmap visualizations
• Period-over-period comparisons

Pivot Table Pro Tips

1. Put time on columns — Months or quarters as columns makes trends easy to read left-to-right
2. Put categories on rows — Products, regions, or segments as rows for easy comparison
3. Add conditional formatting — “Color code the cells by value” makes patterns pop
4. Include totals — “Add row and column totals” for context

Percentages and Ratios

Go beyond raw numbers to meaningful metrics.

Percentage of Total

"What percentage of revenue is each product category?"
"Show customer count as percentage of total by segment"
"Revenue share by region"

Ratios

"Calculate profit margin by product (profit / revenue)"
"Conversion rate by channel (purchases / visits)"
"Average revenue per customer by segment"

Growth Rates

"Month-over-month revenue growth"
"Year-over-year customer growth by segment"
"Calculate growth rate from first to last month"

From Analysis to Action

Aggregate Then Visualize

Charts work better with summarized data:

"Group by month and calculate total revenue, then create a line chart"

Charting 12 monthly totals is clearer than 10,000 daily transactions.

Aggregate Then Export

Reports need summaries, not raw data:

"Group by Region and Category with revenue, orders, and average order value. Export to Excel."

Excel Export Pro Tips

1. Name your columns clearly before exporting—Excel users will thank you
2. Sort meaningfully — by revenue descending, alphabetically, or by date
3. Include the date range in your export filename or add a summary row
4. Round appropriately — $1,234.56 is fine, $1,234.567891 is not

Aggregate Then Dashboard

Build live dashboards from aggregated views:

"Add this regional summary to my Sales Dashboard"
"Create a dashboard widget showing monthly revenue trend"

The aggregation updates automatically when underlying data changes.

Tips for Better Aggregations

Choose Meaningful Groups

Group by dimensions that matter for your question:

• Time: When did things happen?
• Geography: Where did things happen?
• Category: What type of things?
• Customer segment: Who did things?

Watch for Small Groups

Aggregations with few rows can be misleading:

"Show me the count per group as well as the average"

An average of 2 data points isn’t reliable.

Consider Null Handling

Nulls in your grouping column create an awkward “(null)” group:

"Fill null categories with 'Unknown' before grouping"

Name Calculated Columns Clearly

When creating new metrics:

"Calculate total revenue as 'total_revenue' and order count as 'num_orders'"

Clear names make results easier to understand and export cleanly.

Troubleshooting

”My totals don’t match the original data”

Check for:

• Filtered rows — Did you filter before aggregating?
• Null values — Nulls are excluded from sums by default
• Duplicates — Duplicate rows inflate totals

"Compare sum of revenue before and after grouping"

”I have too many groups”

High-cardinality columns (like customer_id) create too many groups:

"Group by segment instead of individual customer"
"Filter to top 10 products before grouping by product"

“My percentages don’t add to 100%”

Possible causes:

• Rounding errors (usually harmless)
• Filters applied inconsistently
• Null values excluded

"Show the raw numbers along with percentages"

”Period comparison shows weird results”

Check that both periods have complete data:

"How many days of data are in each period?"

Comparing a full month to a partial month gives misleading results.

Aggregation Best Practices

1. Start with grouped metrics for exploration — Cast a wide net, then narrow down
2. Use pivots for presentation — Clean grids are easier to scan
3. Always include counts — Know how many records are in each group
4. Check for outliers — One huge value can skew an average
5. Consider median vs mean — Median is more robust for skewed data
6. Export clean, labeled data — Your future self will thank you

Next Steps

With aggregation skills, you’re ready for more advanced analysis:

• Working with Dates — Time-based aggregations
• How to Connect Your Data — Join aggregated results
• Creating Visualizations — Chart your summaries
• Forecaster Tool — Predict from aggregated trends
• Dashboard Basics — Display key metrics