I'm looking at integrating DuckDB into one of my data pipelines, but as I'm new to SQL as a whole I'm working my way through the "DuckDB in Action" book by Needham, Hunger and Simons.
Up to this point everything has worked fine, but I've reached an example where the book gives the query
PIVOT v_power_per_day
ON year(day)
USING round(sum(kWh)) AS total, max(kWh) AS best_day;
and shows what the output should look like (a pivot table with five columns, with the "total" columns rounded to 1dp). However, when I attempt to run the same query in the database, I get the following:
Catalog Error: round is not an aggregate function
Is this a mistake in the book or is there something wrong with my DuckDB installation? And if it is indeed a typo, how would you go about rounding a column produced by an aggregate function?
The problem is that you can only use aggregate functions in the USING clause. This is made more difficult because the result of the PIVOT is multiple total columns with year prefixes.
One solution is to use star expressions like so:
WITH pivoted AS (
PIVOT v_power_per_day
ON year(day)
USING sum(kWh) AS total, max(kWh) AS best_day
)
SELECT COLUMNS('\d+_best_day'),
round(COLUMNS('\d+_total'))
FROM pivoted;
Less elegant, but I believe it gives you what you are looking for.