Hi, new to metabase, i'm using v0.38.2
I'm trying to visualize native query question to pivot table.
But it returns 'Pivot tables can only be used with aggregated queries.'
Here is my aggregated query :
SELECT "public"."product_mutations"."product_item_warehouse_id" AS "product_item_warehouse_id", "public"."product_mutations"."product_mutationable_type" AS "product_mutationable_type", sum("public"."product_mutations"."qty") AS "sum"
FROM "public"."product_mutations"
GROUP BY "public"."product_mutations"."product_item_warehouse_id", "public"."product_mutations"."product_mutationable_type"
ORDER BY "public"."product_mutations"."product_item_warehouse_id" ASC, "public"."product_mutations"."product_mutationable_type" ASC
I make the same logic on custom question and it's working on pivot.
Any replies will be really helpful, thanks
1 Like
Hi @denly93
Metabase cannot understand that you are aggregation in SQL, since it does not parse the query.
https://www.metabase.com/docs/latest/users-guide/05-visualizing-results.html#pivot-table
1 Like
Hi @flamber does it mean we can't use native query question for pivot table visualization?
@denly93 Let me summarize what the documentations says that I linked to:
Pivot tables are only available to simple and custom questions with summarized data. They don’t work for questions that lack aggregate data, and they don’t work for questions written in SQL, as Metabase would need to modify your SQL code in order to calculate subtotals. If you really need to use SQL, the workaround here is to create your question in two steps: first do all the complex things you need to do in SQL, save the results as a question, then use that saved SQL question as the starting point for a new GUI question which summarizes that data.
So that means you create an SQL question without doing aggregation, so it just returns plain rows, and then you use that question as a starting point in the Simple/Custom question, where you then add you aggregations.
But no, you cannot use the new Pivot Table directly with SQL questions.
3 Likes
Thank you @flamber , understood
