Conditional Aggregation & Data Pivoting0%

Advanced Null Handling with NULLIF & COALESCE

Conditional Aggregation & Data Pivoting

Intermediate12 min read•Updated: 2026-09-12

Conditional Aggregation: Data Pivoting & Crosstab Reporting

Relational databases store transactional data vertically (normalized rows). However, business analysts and financial controllers want to see reports formatted horizontally (pivot tables / crosstabs): displaying sales by month across columns (Jan, Feb, Mar), or counting orders by status horizontally (Pending, Shipped, Delivered).

While MySQL does not have a dedicated PIVOT keyword like SQL Server, you can achieve world-class data pivoting using Conditional Aggregation (embedding CASE expressions inside aggregate functions like SUM() and COUNT()).

1. The Concept of Data Pivoting

 NORMALIZED VERTICAL DATA:
 year | quarter | revenue
 2026 | Q1      | 150000.00
 2026 | Q2      | 180000.00
 2026 | Q3      | 210000.00
 2026 | Q4      | 250000.00
7 
 PIVOTED HORIZONTAL REPORT (Columns created from row values!):
 year | Q1_Revenue | Q2_Revenue | Q3_Revenue | Q4_Revenue | Total_Yearly
 2026 | 150000.00  | 180000.00  | 210000.00  | 250000.00  | 790000.00

2. The Core Pattern: `SUM(CASE WHEN ...)`

To pivot data, you combine SUM() with a CASE expression. If the row matches the target category, return the numeric value; otherwise, return 0:

1SELECT 
2    YEAR(order_date) AS sales_year,
3    SUM(CASE WHEN QUARTER(order_date) = 1 THEN total_amount ELSE 0 END) AS Q1_Sales,
4    SUM(CASE WHEN QUARTER(order_date) = 2 THEN total_amount ELSE 0 END) AS Q2_Sales,
5    SUM(CASE WHEN QUARTER(order_date) = 3 THEN total_amount ELSE 0 END) AS Q3_Sales,
6    SUM(CASE WHEN QUARTER(order_date) = 4 THEN total_amount ELSE 0 END) AS Q4_Sales,
7    SUM(total_amount) AS Total_Annual_Sales
8FROM orders
9GROUP BY YEAR(order_date)
10ORDER BY sales_year DESC;

3. Conditional Counting: `COUNT(CASE WHEN ...)`

When counting occurrences of specific conditions horizontally, exploit the fact that COUNT() ignores NULLs:

1SELECT 
  department_id,
  COUNT(*) AS total_staff,
  
  -- Method A: Using SUM with 1 and 0
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_employees,
  
  -- Method B: Using COUNT with NULL (COUNT ignores NULL!)
  COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_earners_count,
  COUNT(CASE WHEN salary < 40000 THEN 1 END) AS entry_level_count
11FROM employees
12GROUP BY department_id;

4. Real-World E-Commerce Dashboard Pivot

Let us generate a high-level summary showing order statuses broken down by customer acquisition channel:

1SELECT 
  acquisition_channel,
  COUNT(order_id) AS total_orders_placed,
  SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END) AS delivered_orders,
  SUM(CASE WHEN order_status = 'Pending'   THEN 1 ELSE 0 END) AS pending_orders,
  SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
  
  -- Calculate cancellation rate percentage on the fly!
  ROUND(
      (SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(order_id)) * 100, 
      2
  ) AS cancellation_rate_pct
13FROM orders
14GROUP BY acquisition_channel;

5. Best Practices & Common Pitfalls

Do Not Use COUNT(CASE WHEN cond THEN 0 END): A common beginner bug! If you write COUNT(CASE WHEN status = 'Paid' THEN 1 ELSE 0 END), MySQL counts both 1 and 0 because 0 is a non-null value! Always use ELSE NULL (or omit ELSE) with COUNT(), or use SUM() with ELSE 0.
Performance Benefits: Conditional aggregation computes multiple metrics across an entire table in a single table scan, replacing what would otherwise require 5 or 6 separate join queries!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is "Conditional Aggregation" in relational SQL?

Question 2

Why does COUNT(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) fail to count only shipped orders accurately?

Question 3

Which construct correctly sums sales revenue strictly for the month of January in a pivoted yearly report?

Question 4

What is the primary performance advantage of conditional aggregation over joining multiple filtered subqueries?

Question 5

In a pivoted query calculating cancellation rates, what function prevents a division by zero error if COUNT(order_id) is 0?

Next Lesson

Advanced Null Handling with NULLIF & COALESCE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Searched CASE Expression	Advanced Null Handling with NULLIF & COALESCE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Advanced Null Handling with NULLIF & COALESCE

Conditional Aggregation & Data Pivoting

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Conditional Aggregation: Data Pivoting & Crosstab Reporting

1. The Concept of Data Pivoting

 NORMALIZED VERTICAL DATA:
 year | quarter | revenue
 2026 | Q1      | 150000.00
 2026 | Q2      | 180000.00
 2026 | Q3      | 210000.00
 2026 | Q4      | 250000.00
7 
 PIVOTED HORIZONTAL REPORT (Columns created from row values!):
 year | Q1_Revenue | Q2_Revenue | Q3_Revenue | Q4_Revenue | Total_Yearly
 2026 | 150000.00  | 180000.00  | 210000.00  | 250000.00  | 790000.00

2. The Core Pattern: `SUM(CASE WHEN ...)`

To pivot data, you combine SUM() with a CASE expression. If the row matches the target category, return the numeric value; otherwise, return 0:

1SELECT 
2    YEAR(order_date) AS sales_year,
3    SUM(CASE WHEN QUARTER(order_date) = 1 THEN total_amount ELSE 0 END) AS Q1_Sales,
4    SUM(CASE WHEN QUARTER(order_date) = 2 THEN total_amount ELSE 0 END) AS Q2_Sales,
5    SUM(CASE WHEN QUARTER(order_date) = 3 THEN total_amount ELSE 0 END) AS Q3_Sales,
6    SUM(CASE WHEN QUARTER(order_date) = 4 THEN total_amount ELSE 0 END) AS Q4_Sales,
7    SUM(total_amount) AS Total_Annual_Sales
8FROM orders
9GROUP BY YEAR(order_date)
10ORDER BY sales_year DESC;

3. Conditional Counting: `COUNT(CASE WHEN ...)`

When counting occurrences of specific conditions horizontally, exploit the fact that COUNT() ignores NULLs:

1SELECT 
  department_id,
  COUNT(*) AS total_staff,
  
  -- Method A: Using SUM with 1 and 0
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_employees,
  
  -- Method B: Using COUNT with NULL (COUNT ignores NULL!)
  COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS high_earners_count,
  COUNT(CASE WHEN salary < 40000 THEN 1 END) AS entry_level_count
11FROM employees
12GROUP BY department_id;

4. Real-World E-Commerce Dashboard Pivot

Let us generate a high-level summary showing order statuses broken down by customer acquisition channel:

1SELECT 
  acquisition_channel,
  COUNT(order_id) AS total_orders_placed,
  SUM(CASE WHEN order_status = 'Delivered' THEN 1 ELSE 0 END) AS delivered_orders,
  SUM(CASE WHEN order_status = 'Pending'   THEN 1 ELSE 0 END) AS pending_orders,
  SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
  
  -- Calculate cancellation rate percentage on the fly!
  ROUND(
      (SUM(CASE WHEN order_status = 'Cancelled' THEN 1 ELSE 0 END) / COUNT(order_id)) * 100, 
      2
  ) AS cancellation_rate_pct
13FROM orders
14GROUP BY acquisition_channel;

5. Best Practices & Common Pitfalls

Do Not Use COUNT(CASE WHEN cond THEN 0 END): A common beginner bug! If you write COUNT(CASE WHEN status = 'Paid' THEN 1 ELSE 0 END), MySQL counts both 1 and 0 because 0 is a non-null value! Always use ELSE NULL (or omit ELSE) with COUNT(), or use SUM() with ELSE 0.
Performance Benefits: Conditional aggregation computes multiple metrics across an entire table in a single table scan, replacing what would otherwise require 5 or 6 separate join queries!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is "Conditional Aggregation" in relational SQL?

Question 2

Why does COUNT(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) fail to count only shipped orders accurately?

Question 3

Which construct correctly sums sales revenue strictly for the month of January in a pivoted yearly report?

Question 4

What is the primary performance advantage of conditional aggregation over joining multiple filtered subqueries?

Question 5

In a pivoted query calculating cancellation rates, what function prevents a division by zero error if COUNT(order_id) is 0?

Next Lesson

Advanced Null Handling with NULLIF & COALESCE

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Searched CASE Expression	Advanced Null Handling with NULLIF & COALESCE

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Conditional Aggregation: Data Pivoting & Crosstab Reporting

1. The Concept of Data Pivoting

2. The Core Pattern: SUM(CASE WHEN ...)

3. Conditional Counting: COUNT(CASE WHEN ...)

4. Real-World E-Commerce Dashboard Pivot

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Advanced Null Handling with NULLIF & COALESCE

Related Lessons

Practice Quiz

Loading Page Content...

Conditional Aggregation: Data Pivoting & Crosstab Reporting

1. The Concept of Data Pivoting

2. The Core Pattern: SUM(CASE WHEN ...)

3. Conditional Counting: COUNT(CASE WHEN ...)

4. Real-World E-Commerce Dashboard Pivot

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Advanced Null Handling with NULLIF & COALESCE

Related Lessons

Practice Quiz

2. The Core Pattern: `SUM(CASE WHEN ...)`

3. Conditional Counting: `COUNT(CASE WHEN ...)`

2. The Core Pattern: `SUM(CASE WHEN ...)`

3. Conditional Counting: `COUNT(CASE WHEN ...)`