Get Conditional Counts in a Single Query with CASE

SELECT
    date(order_date) AS order_day,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN total_amount > 100 THEN 1 ELSE 0 END) AS high_value_orders
FROM orders
GROUP BY order_day
ORDER BY order_day;

How it works: This query aggregates order data by day, providing not just the total number of orders, but also conditional counts for specific criteria like 'completed' orders, 'pending' orders, and 'high-value' orders (total amount > 100). It uses `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` to count occurrences based on conditions within the `GROUP BY` context, providing a powerful summary in a single result set without pivoting.