Perform Conditional Aggregation for Pivot-like Results

SELECT
  YEAR(order_date) AS order_year,
  MONTH(order_date) AS order_month,
  SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_sales,
  SUM(CASE WHEN status = 'pending' THEN total_amount ELSE 0 END) AS pending_sales,
  SUM(CASE WHEN status = 'cancelled' THEN total_amount ELSE 0 END) AS cancelled_sales
FROM
  orders
GROUP BY
  order_year, order_month
ORDER BY
  order_year, order_month;

How it works: This query demonstrates conditional aggregation to create a pivot-like report. It groups orders by year and month, then uses `SUM` with `CASE` statements to sum `total_amount` based on the `status` of each order. This effectively transforms row data (different statuses) into column data (e.g., `completed_sales`, `pending_sales`), providing a summarized view by period. This technique is often used for generating cross-tabulated reports.