Course contentsShow
← Python
Lesson 835 of 1,06013. Data Integration and Web APIsFree lesson

Aggregating Data with SQL Functions

Learners will perform analytical calculations on stored data using aggregate functions like SUM, AVG, and COUNT within SQL queries.

Aggregating Data with SQL Functions

In this lesson, you will learn how to perform analytical calculations directly within your database queries to summarize large datasets efficiently using SQL aggregate functions.

When working with large collections of records—such as data fetched from an API and stored in your SQLite database—you often don't need every individual row. Instead, you need insights like totals, averages, or counts. Rather than pulling all data into Python memory to perform these calculations, you can instruct the database engine to do the heavy lifting using aggregate functions.

Core Aggregate Functions

The most common aggregate functions include:

  • COUNT(): Returns the number of rows that match your criteria.
  • SUM(): Calculates the total sum of a numeric column.
  • AVG(): Computes the average value of a numeric column.
  • MIN() / MAX(): Finds the smallest or largest value in a set.

Implementation in Python

Using the sqlite3 library, you execute these functions just like standard SELECT queries. The database returns a single row containing the computed result.

import sqlite3

connection = sqlite3.connect("data_warehouse.db")
cursor = connection.cursor()

# Calculate the average price of items stored in our database
cursor.execute("SELECT AVG(price) FROM products WHERE category = ?", ('Electronics',))
average_price = cursor.fetchone()[0]

# Count how many products we currently track
cursor.execute("SELECT COUNT(*) FROM products")
total_products = cursor.fetchone()[0]

print(f"Average Electronics Price: ${average_price:.2f}")
print(f"Total inventory count: {total_products}")

connection.close()

Why Use Aggregates?

Performance is the primary advantage. If you have 100,000 records, fetching them all into Python to sum them up in a list comprehension is memory-intensive and slow. By using SUM() or AVG() within the SQL statement, the database performs the math internally and only sends a tiny, single-value result back to your application. This approach minimizes network latency and keeps your Python memory footprint lightweight, which is essential when integrating high-frequency API data into your local storage.

Key Takeaway: Use SQL aggregate functions to perform calculations directly within the database to minimize memory usage and improve the speed of your data analysis.