Pivot table with row and column totals using native SQL

Get Help
1

Hi everyone,

I’m using Metabase with PostgreSQL and need Pivot tables for a very specific reason.

Data comes from multiple tables (core entity + status + custom fields in a separate table)

I need a Pivot table with:

  • Rows: custom field value (e.g. “Source”)

  • Columns: status

  • Values: count of rows

  • Row totals and column totals enabled

The main reason I need Pivot is not aggregation itself but correct automatic placement of totals.

When totals are calculated as separate SQL metrics or columns, they become regular columns, and I cant place them at the end reliably.

Pivot tables handle totals correctly by design.

If aggregation is done in SQL (GROUP BY source, status), Metabase says: “Pivot tables can only be used with aggregated queries”

Is there a recommended or canonical way to make the pivot table for this task?

Thanks in advance!

2

From the docs:

Pivot tables are currently only supported for questions built in the query builder.

The workaround is to create a Model of your SQL query, then use the query builder to build your pivot. You might have to restructure the Model to allow the question to do the necessary aggregation.

If you need to append rows in SQL use UNION ALL.

3

Thanks a lot! Issue solved