SQLDailyDrills

STRING_AGG, FILTER and ROLLUP

Join group values into text, count by condition, and add subtotal rows.

Syntax

STRING_AGG(column, separator [ORDER BY …])
aggregate(…) FILTER (WHERE condition)
GROUP BY ROLLUP (column1, column2)
  • MySQL calls STRING_AGG GROUP_CONCAT; SQL Server uses STRING_AGG too.
  • ROLLUP adds a subtotal for each leading column and a grand total.

Example

SELECT channel, COUNT(*) AS orders FROM orders GROUP BY ROLLUP (channel) ORDER BY channel;

Orders per channel plus a total row where channel is NULL.

Tables used: orders. Try it in a lesson below.

Common mistakes

  • In a ROLLUP result, a NULL in the grouped column can be a subtotal row or a real NULL. GROUPING() tells them apart.

Practise it