3.5 (2)

3.5 (2)

#### Overview of this book

Preface
1. Introduction to SQL for Analytics
Free Chapter
2. SQL for Data Preparation
3. Aggregate and Window Functions
4. Importing and Exporting Data
5. Analytics Using Complex Data Types
6. Performant SQL
7. The Scientific Method and Applied Problem Solving

# 3. Aggregate and Window Functions

## Activity 3.01: Analyzing Sales Data Using Aggregate Functions

Solution

1. Open your favorite SQL client and connect to the `sqlda` database.
2. Calculate the number of unit sales the company has achieved using the `COUNT` function:
```SELECT
COUNT(*)
FROM
sales;```

You should get `37,711` sales.

3. Determine the total sales amount in dollars for each state; we can use the `SUM` aggregate function here:
```SELECT
c.state, SUM(sales_amount) as total_sales_amount
FROM
sales s
INNER JOIN
customers c
ON c.customer_id=s.customer_id
GROUP BY
1
ORDER BY
1;```

You will get the following output:

Figure 3.30: Total sales in dollars by US state

4. Determine the top five dealerships in terms of most units sold using the `GROUP BY` clause. Set the `LIMIT` to `5`:
```SELECT
s.dealership_id,
COUNT(*)
FROM
sales s
WHERE
...```