4. Aggregate Functions for Data Analysis

Activity 6: 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 by 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 4.23: Total sales in dollars by US state

4. Determine the top five dealerships in terms of most units sold, using the GROUP BY clause and set LIMIT as 5:

   SELECT s.dealership_id, COUNT(*)
   FROM sales s
   WHERE channel='dealership'
   GROUP BY 1
   ORDER BY 2 DESC
   LIMIT 5

   You should get the following output:

   Figure 4.24: Top five dealerships by units sold

5. Calculate...