Summarize data

TutorialApplies to: Unified engine deployments

Calculate totals and compare groups without creating a table.

Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables. Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. WITH names input data for one statement only. Each example can run on its own.

1. Calculate totals

How many orders and how much sales?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT COUNT(*) AS orders, SUM(amount) AS sales FROM orders;

Expected result

orders	sales
6	140

Aggregates combine input rows into a summary. Change one thing: Add WHERE region = ‘North’. Expect 4 orders and sales of 85.

2. Compare categories

What are sales by category?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT category, SUM(amount) AS sales FROM orders GROUP BY category ORDER BY category;

Expected result

category	sales
Books	60
Games	45
Garden	35

GROUP BY defines the groups to summarize. Change one thing: Use region instead of category in SELECT, GROUP BY, and ORDER BY. Expect North 85 and South 55.

3. Use two dimensions

What are sales by category and region?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT category, region, SUM(amount) AS sales FROM orders GROUP BY category, region ORDER BY category, region;

Expected result

category	region	sales
Books	North	60
Games	North	15
Games	South	30
Garden	North	10
Garden	South	25

Each distinct pair forms a group. Change one thing: Add COUNT(*) AS orders. Books/North has 2; all other groups have 1.

4. Filter groups

Which categories exceed 40 in sales?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT category, SUM(amount) AS sales FROM orders GROUP BY category HAVING SUM(amount) > 40 ORDER BY category;

Expected result

category	sales
Books	60
Games	45

HAVING filters after grouping. Change one thing: Change 40 to 50. Only Books remains.

5. Count conditionally

How many large orders are in each region?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT region, SUM(CASE WHEN amount >= 30 THEN 1 ELSE 0 END) AS large_orders FROM orders GROUP BY region ORDER BY region;

Expected result

region	large_orders
North	1
South	1

CASE can choose the values passed to an aggregate. Change one thing: Use amount >= 25. South has 2 and North has 1.

6. Count distinct categories

How many categories does each region contain?

WITH orders(id, category, region, amount) AS (
  VALUES (1, 'Books', 'North', 20), (2, 'Games', 'South', 30),
         (3, 'Games', 'North', 15), (4, 'Books', 'North', 40),
         (5, 'Garden', 'South', 25), (6, 'Garden', 'North', 10)
)
SELECT region, COUNT(DISTINCT category) AS categories FROM orders GROUP BY region ORDER BY region;

Expected result

region	categories
North	3
South	2

A distinct count removes repeated values within each group. Change one thing: Use COUNT(*) instead. North has 4 orders and South has 2.

Continue

Continue to Combine datasets, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Before you start

1. Calculate totals

2. Compare categories

3. Use two dimensions

4. Filter groups

5. Count conditionally

6. Count distinct categories

Continue

​Before you start

​1. Calculate totals

​2. Compare categories

​3. Use two dimensions

​4. Filter groups

​5. Count conditionally

​6. Count distinct categories

​Continue

Before you start

1. Calculate totals

2. Compare categories

3. Use two dimensions

4. Filter groups

5. Count conditionally

6. Count distinct categories

Continue