Generate Cross-Tabulated Reports Using Conditional Aggregation

SELECT
    product_category,
    SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) AS completed_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,
    COUNT(*) AS total_orders
FROM
    orders
GROUP BY
    product_category
ORDER BY
    product_category;

How it works: This query demonstrates how to generate a cross-tabulated report using conditional aggregation. It counts the number of orders for each product category based on their status (completed, pending, cancelled) in a single row. The `CASE` statement within the `SUM` aggregate function checks the `order_status` and effectively counts `1` for a match or `0` otherwise, allowing you to pivot specific status counts into separate columns without complex dynamic SQL.