Reports often want one column per category or per month instead of one row each. The portable way is conditional aggregation:
SELECT EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned
FROM orders
GROUP BY 1;
Each column is an aggregate over only the rows that match its condition. Combinations with no rows come out as 0 with ELSE 0, or NULL with SUM(CASE ... END) and no ELSE.
SQL Server, Oracle, Snowflake and DuckDB also have a PIVOT operator. Here it reads PIVOT table ON column USING aggregate GROUP BY row_column; SQL Server writes PIVOT (SUM(x) FOR column IN ([a], [b])). PostgreSQL has no PIVOT; use conditional aggregation.
The reverse, unpivot, turns columns into rows: one SELECT per column joined with UNION ALL, or the UNPIVOT operator where it exists.