Conditional Aggregation for Data Pivoting

Snippet

Conditional Aggregation for Data Pivoting

Conditional aggregation uses CASE statements inside aggregate functions to pivot data from rows into columns. This technique is highly efficient for generating reports that require multiple metrics from a single scan of the table.

snippet.sql
sql
SELECT 
  EXTRACT(YEAR FROM order_date) as year,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) as revenue_shipped,
  SUM(CASE WHEN status = 'returned' THEN amount ELSE 0 END) as loss_returned
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

Breakdown

EXTRACT(YEAR FROM order_date)

Retrieves the year component from a date or timestamp field.

SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END)

Only adds the amount to the sum if the specific condition is met, effectively filtering within the sum.

GROUP BY EXTRACT(YEAR FROM order_date)

Aggregates the results per year.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

Conditional Aggregation for Data Pivoting

Related