4. Aggregate Functions for Data Analysis

Activity 4.01: Analyzing Sales Data Using Aggregate Functions

Solution:

1. Open pgAdmin, connect to the sqlda database, and open SQL query editor.
2. Calculate the total number of unit sales the company has made:

   SELECT 
     COUNT(*)
   FROM 
     sales;

The result is as follows:

Figure 4.29: Result of COUNT(*) for sales units

Note that because each sales transaction contains a product ID, there is no NULL value in the product_id column. So, COUNT(product_id) will also work. Similarly, COUNT(sales_amount) will also work.

3. Calculate the total sales amount in dollars for each state:

   SELECT 
     c.state, 
     SUM(s.sales_amount)::DECIMAL(12,2)
   FROM 
     sales s
   JOIN 
     customers c
   ON 
     s.customer_id = c.customer_id
   GROUP BY 
     c.state
   ORDER BY 
     1;

The result is as follows:

Figure 4.30: Result of sales by state

4. Identify the top five best dealerships in terms of the most units...