> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Summarize data

> Calculate totals and compare groups without creating a table.

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/combine-data.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.