Mastering Data Aggregation in PostgreSQL: Because Counting Stuff is Cool!

PostgreSQL

"A good database is like a healthy relationship: stable, well-structured, and doesn’t crash easily. Start learning PostgreSQL now!"

Ever wondered how many pizzas you've ordered in the last month? Time to GROUP BY & COUNT!

Introduction: Why Aggregate Data?

Imagine you run a pizza shop, and you need answers to life’s most important questions:

How many orders did we receive today? (COUNT)
What’s the average pizza price? (AVG)
Which topping is the most popular? (GROUP BY + COUNT)
Who spent the most money? (SUM)

Instead of manually counting, PostgreSQL has powerful aggregation functions that do the heavy lifting for you!

Aggregation = Summarizing large amounts of data into useful insights.
Think of it as your data’s "TL;DR".

The Magic of GROUP BY: Sorting Data

When you need grouped summaries, PostgreSQL’s GROUP BY is your best friend.
It organizes your rows into categories before applying an aggregation function.

Example: Counting Orders Per Customer

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

How It Works:

GROUP BY customer_id groups the rows by customer ID.
COUNT(*) counts the total number of orders for each customer.

Analogy:
If your teacher groups students by hair color, and then counts them, that’s GROUP BY + COUNT.

COUNT(): Because We Love Counting Stuff!

COUNT() is the easiest aggregation function—it simply counts rows.

Example: Counting Customers Who Ordered Pizza 

SELECT COUNT(*) AS total_customers
FROM orders;

 Variations of COUNT():

COUNT Function	What It Does
COUNT(*)	Counts all rows (even if they have NULL values)
COUNT(column_name)	Counts only non-null values
COUNT(DISTINCT column_name)	Counts unique values

Example: Counting Unique Customers Who Ordered Pizza

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

 SUM(): Adding Up Your Profits

Need to sum up sales, revenue, or calories from all those pizzas? Use SUM()!

Example: Calculating Total Revenue 

SELECT SUM(price) AS total_revenue
FROM orders;

 Real-Life Example:

• Counting your salary for the year? SUM(salary)
• Total hours worked by employees? SUM(hours_worked)

 SUM() only works on numeric columns!

AVG(): Finding the Average Like a Math Genius

AVG() helps you find the average value of a column.

Example: What’s the Average Pizza Price?

SELECT AVG(price) AS avg_pizza_price
FROM orders;

 Great for:

• Finding the average customer spending (AVG(total_spent))
• Calculating the average employee salary (AVG(salary))
• Measuring average order value in e-commerce

 AVG() only works on numeric data types!

MIN() & MAX(): Finding Extremes Like a Boss

Function	What It Does
MIN(column)	Finds the smallest value in a column
MAX(column)	Finds the largest value in a column

Example: Finding Cheapest & Most Expensive Pizza

SELECT MIN(price) AS cheapest_pizza, MAX(price) AS most_expensive_pizza
FROM orders;

Useful for:
Finding youngest/oldest employee (MIN(age), MAX(age))
Checking lowest & highest sales (MIN(sales), MAX(sales))

HAVING: Filtering Aggregated Data

 HAVING is WHERE for aggregated data!

Since WHERE filters rows, but GROUP BY works on groups, we need HAVING to filter after aggregation.

Example: Show Only Customers Who Ordered More Than 5 Pizzas

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Why Not WHERE?
Because WHERE filters before grouping, while HAVING filters after aggregation.

WHERE → Filters raw data
HAVING → Filters grouped data

Real-World Example: Sales Dashboard Query

Let’s say we want a sales report showing:

• Total sales per customer
• Average order value
• Only include customers who spent more than $500

SELECT customer_id, 
       COUNT(*) AS total_orders,
       SUM(price) AS total_spent,
       AVG(price) AS avg_order_value
FROM orders
GROUP BY customer_id
HAVING SUM(price) > 500;

Aggregation Cheat Sheet

Function	What It Does	Example
COUNT(*)	Counts total rows	COUNT(*)
SUM(column)	Adds up values	SUM(sales)
AVG(column)	Finds average value	AVG(price)
MIN(column)	Finds smallest value	MIN(salary)
MAX(column)	Finds largest value	MAX(age)
GROUP BY column	Groups rows before aggregating	GROUP BY customer_id
HAVING condition	Filters after aggregation	HAVING SUM(price) > 500

Why Aggregation is Your Best Friend

Without aggregation:
We’d manually count everything
We'd struggle to summarize large datasets
Our SQL queries would be long & inefficient

With aggregation:
We summarize thousands of rows in seconds
We find insights easily (average, total, min, max)
We filter aggregated results with HAVING