SQLite Aggregation Functions: Practical Data Statistics and Analysis Core Tool

In data querying, aggregate functions serve as the core tools for statistics and analysis. SQLite supports aggregate functions such as COUNT, SUM, AVG, MAX, and MIN, which perform calculations on a set of data to help developers quickly obtain key metrics.

Detailed Explanation of Common Aggregate Functions

1. COUNT: Counts the number of rows or records satisfying specific conditions

   SELECT COUNT(*) FROM sales; -- Returns the total number of records
   SELECT COUNT(COLUMN_NAME) FROM sales; -- Counts the number of values in a specific column
   

2. SUM: Calculates the sum of values in a numeric column

   SELECT SUM(price) FROM sales; -- Calculates the total amount of all orders
   SELECT SUM(price) OVER() FROM sales; -- Calculates the sum within a window frame
   

3. AVG: Calculates the average value of a numeric column

   SELECT AVG(price) FROM sales; -- Calculates the average order amount
   

4. MAX: Retrieves the maximum value from a numeric column

   SELECT MAX(price) FROM sales; -- Finds the highest order amount
   

5. MIN: Retrieves the minimum value from a numeric column

   SELECT MIN(price) FROM sales; -- Finds the lowest order amount
   

Practical Case: Sales Data Analysis

Assume there is a sales table containing fields id, date, and price. The following metrics need to be calculated:

• Total sales revenue
• Average sales revenue
• Maximum and minimum order amounts
• Number of orders per month

Query Example

SELECT 
    SUM(price) AS total_sales, 
    AVG(price) AS average_sales, 
    MAX(price) AS max_price, 
    MIN(price) AS min_price, 
    COUNT(*) AS total_orders 
FROM sales 
WHERE date BETWEEN '2023-01-01' AND '2023-06-30';

Key Techniques and Precautions

1. GROUP BY: Used to group and aggregate data across multiple columns

   SELECT DATE, COUNT(*) AS order_count 
   FROM sales 
   GROUP BY DATE;
   

2. HAVING: Filters grouped results that satisfy specific conditions

   SELECT DATE, COUNT(*) AS order_count 
   FROM sales 
   GROUP BY DATE 
   HAVING COUNT(*) > 10;
   

3. Performance Optimization: Avoid using SELECT *; use indexed columns; avoid SELECT TOP.
4. NULL Value Handling: Aggregate functions default to ignoring NULL values; pay attention to data cleaning.

Deep Practice: Complex Analysis

By combining JOIN with aggregate functions, multi-table statistical analysis can be achieved. For example:

SELECT 
    s.name, 
    COUNT(DISTINCT p.id) AS product_count 
FROM sales s 
JOIN products p ON s.product_id = p.id 
GROUP BY s.name;

This query counts the number of orders for each product category. Ensure that the id field in the products table matches the product_id field in the sales table.

Summary

Aggregate functions are the core tools for implementing data statistics and analysis in SQLite. By mastering functions such as COUNT, SUM, and AVG, developers can efficiently extract key business metrics. In practice, focus on performance optimization, NULL value handling, and grouping logic to ensure query results are accurate and reliable.