Unpivot Then Aggregate — Solution & ExplanationProblems / Unpivot Then Aggregate / EditorialApproach

Unpivot wide to long. Each metric column becomes its own SELECT: the column name as a string literal ('visits' AS metric) and the column's number under a shared alias (visits AS value). UNION ALL stacks the three selects into a (metric, value) long table — 20 days × 3 metrics = 60 rows.

Aggregate the long form. GROUP BY metric + SUM(value) produces one total per metric — three rows.

DuckDB also supports this directly with UNPIVOT daily_metrics ON visits, signups, purchases INTO NAME metric VALUE value, but UNION ALL is portable to any dialect. In PySpark, the same idea is one select per column with F.lit for the metric name, chained with .unionAll, then groupBy('metric').agg(F.sum('value')).Key InsightAggregating across *columns* is awkward — SUM works down rows, not across a row. After reshaping wide to long, "total per metric" becomes the most ordinary GROUP BY imaginable. Reshape first and the aggregation writes itself; the same trick powers most reporting pipelines: keep (or reshape) data long, aggregate cheap.Hints

Wide-to-long is UNION ALL: one SELECT per metric column, each emitting the column name as a string literal and the column's number under a common alias like value.

Once the data is long, the total per metric is an ordinary GROUP BY metric + SUM(value). DuckDB also has UNPIVOT ... ON visits, signups, purchases syntax as a shortcut.

In PySpark, build one df.select(F.lit('visits').alias('metric'), F.col('visits').alias('value')) per column, chain them with .unionAll, then groupBy('metric').agg(F.sum('value')).

Solve Unpivot Then Aggregate yourself →