GROUP BY in PostgreSQL 🚀

The GROUP BY clause in PostgreSQL is used to group rows that have the same values in specified columns. It is often used with aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to summarize data.

1. Basic Syntax

SELECT column1, aggregate_function(column2)FROM table_nameGROUP BY column1;

• column1 → The column used for grouping.
• aggregate_function(column2) → The function applied to each group.

2. Example: Count Employees Per Department

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY department;

🔹 Groups employees by department and counts them.

3. Example: Calculate Total Sales Per Customer

SELECT customer_id, SUM(amount) AS total_spentFROM ordersGROUP BY customer_id;

🔹 Groups orders by customer and calculates total spent.

4. Example: Find Average Salary Per Department

SELECT department, AVG(salary) AS avg_salaryFROM employeesGROUP BY department;

🔹 Groups employees by department and finds average salary.

5. Example: Get Maximum and Minimum Prices by Category

SELECT category, MAX(price) AS max_price, MIN(price) AS min_priceFROM productsGROUP BY category;

🔹 Groups products by category and finds the highest and lowest price.

6. Example: Count Unique Roles in Each Department

SELECT department, COUNT(DISTINCT role) AS unique_rolesFROM employeesGROUP BY department;

🔹 Groups employees by department and counts unique roles.

7. Example: Filter Grouped Results Using HAVING

SELECT customer_id, SUM(amount) AS total_spentFROM ordersGROUP BY customer_idHAVING SUM(amount) > 500;

🔹 Returns customers who spent more than $500.

8. Example: Combine GROUP BY with ORDER BY

SELECT category, COUNT(*) AS product_countFROM productsGROUP BY categoryORDER BY product_count DESC;

🔹 Groups products by category, counts them, and sorts in descending order.

9. Example: Group By Multiple Columns

SELECT department, role, COUNT(*) AS total_employeesFROM employeesGROUP BY department, role;

🔹 Groups by both department and role.

10. Example: Group By with Date Truncation

SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS monthly_salesFROM ordersGROUP BY order_monthORDER BY order_month;

🔹 Groups orders by month and calculates monthly sales.

11. Example: Group By with Joins

SELECT u.name, COUNT(o.id) AS order_countFROM users uJOIN orders o ON u.id = o.user_idGROUP BY u.name;

🔹 Groups orders by user and counts them.

12. Difference Between GROUP BY and DISTINCT

Example Comparison

Using GROUP BY

SELECT category, COUNT(*) FROM products GROUP BY category;

🔹 Groups products by category and counts them.

Using DISTINCT

SELECT DISTINCT category FROM products;

🔹 Returns unique product categories, but doesn’t count them.

13. Performance Optimization

• Indexes on GROUP BY columns improve performance.
• Use HAVING only when necessary, as filtering in WHERE is faster.
• Pre-aggregate data using materialized views if querying large datasets.

Next Steps

✅ Combine GROUP BY with HAVING, ORDER BY, and JOIN
✅ Optimize queries for performance
✅ Implement GROUP BY in Go Fiber queries