📊 Database for Software Developers – Data Transformation, Grouping & Aggregation (Part 2)

Welcome back to the Database for Software Developers series. In this post, we’re diving deeper into one of the most powerful tools in SQL: aggregation, grouping, and a few advanced tricks to help you write more insightful queries.

Whether you're building an analytics dashboard or just trying to understand your dataset better, mastering GROUP BY, aggregate functions, and conditional transformations is a must.

Let’s get our hands dirty with examples! 💻

✨ 1. What is Aggregation?

Aggregation allows you to perform calculations across multiple rows and return a single summarized result.

Common SQL aggregate functions:

• COUNT() – Total number of records
• SUM() – Total value
• AVG() – Average value
• MIN() – Lowest value
• MAX() – Highest value
• GROUP_CONCAT() – Combines values into a comma-separated string

🧪 Example:

SELECT MAX(buy_price) AS max_price, MIN(buy_price) AS min_price, ROUND(AVG(buy_price), 2) AS average_price FROM products; 

This gives you a summary of price stats across all products.

🧮 2. Aggregation with Filters

Sometimes, you want to summarize data with a condition.

🧪 Example:

SELECT SUM(qty_in_stock) FROM products WHERE product_line = 'Motorcycles'; 

This gives you the total number of motorcycles in stock.

📦 3. Aggregating Expressions

Yes, you can perform math inside SUM()!

🧪 Example:

SELECT order_id, SUM(quantity_ordered * price_each) AS total FROM orderd_items WHERE order_id = 10010; 

This calculates the total order value by multiplying quantity and price.

📅 4. Counting Records Based on Time Range

Need to know how many orders were placed in 2003?

🧪 Example:

SELECT COUNT(*) AS order_of_2003 FROM orders WHERE order_date BETWEEN '2003-01-01' AND '2003-12-31'; 

📊 5. Aggregation with Grouping

You can group your results by categories like product_line.

⚠️ Note:

There's a small typo in the original snippet: qty_in_stick should be qty_in_stock.

🧪 Example:

SELECT product_line, SUM(qty_in_stock) AS total_stock FROM products GROUP BY product_line; 

🔄 6. DISTINCT in Aggregation

Use DISTINCT to avoid counting duplicates, especially with joins.

🧪 Example:

SELECT product_line, SUM(qty_in_stock) AS total_stock, COUNT(DISTINCT vendor) AS unique_vendors FROM products GROUP BY product_line; 

🧠 7. Conditional Aggregation (Smart Grouping)

Imagine you're sending gifts:

• Use DHL for non-USA employees
• Use USMail for USA employees

🧪 Example:

SELECT SUM(IF(o.country = 'USA', 0, 1)) AS DHL, SUM(IF(o.country = 'USA', 1, 0)) AS USMail FROM employees e JOIN offices o ON o.code = e.office_code; 

🚚 8. Shipment Cost Calculation Based on Location

Let’s say shipping costs vary:

• $100 within USA
• $300 outside USA

You want the total cost and number of orders per salesperson.

🧪 Example:

SELECT c.sales_rep_id, COUNT(DISTINCT o.id) AS total_orders, SUM(IF(c.country = 'USA', 100, 300)) AS total_shipment_cost FROM orders o JOIN customers c ON c.id = o.customer_id GROUP BY c.sales_rep_id; 

📅 9. Grouping by Multiple Columns (Advanced)

You can group by more than one field. For instance, let’s see payment info by customer per year, and get a list of checks.

🧪 Example:

SELECT customer_id, YEAR(payment_date) AS payment_year, GROUP_CONCAT(check_number) AS all_checks, SUM(amount) AS total FROM payments GROUP BY customer_id, payment_year;