📊 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;