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 
     ...