I've run into the issue several times where I'm trying to build a bar chart with count()s from a group of data.
My ideal query structure is something like this:
select
count(distinct user_id) as "Delivered",
count(distinct user_id) filter (where event_type = 'OPEN') as "Opened",
count(distinct user_id) filter (where event_type = 'LINK_CLICK') as "Link Clicked"
from base
Which returns a group of data I would think is optimal for a bar chart:
But I am only presented with the Table option in Metabase.
The workaround I'm forced to use is unnesting two arrays. Which puts the data in the correct format, but seems like a janky way to format the query.
select
unnest(ARRAY['Delivered', 'Opened', 'Link Clicked']) as status,
unnest(ARRAY[
(select count(distinct user_id) from base),
(select count(distinct user_id) from base where event_type = 'OPEN'),
(select count(distinct user_id) from base where event_type = 'LINK_CLICK')
]) as count
Which produces this format:
The above gives me all of the chart options I would like to use. But it just seems like an unnecessary formatting step for the data. Would be happy to hear of any better options out there.
Hi @themanmaran
Doing transposing can be tricky, depending on your database type and what you are trying to return.
https://github.com/metabase/metabase/issues/3769 - upvote by clicking 
on the first post
You could do something like this though:
Where the fake x-axis is just 1 * 1, and the open/link_click is a custom expression like Distinct(case([Event Type] = "OPEN", [User ID]))
You can hide the X-axis in the Axes settings.
1 Like
Ah I see the discussion around it now. Looks like an ongoing issue for 6 years now lol.
It seems to me that this would be ideal as a client side switch (perhaps with a limit on rows).
i.e. click "Transpose Data" would flip:
delivered | opened | clicked
1 500 250 100
to:
delivered | 500
opened | 250
clicked | 100
It seems like the main counterpoint was the CSV output not matching what the client side displayed. I don't see a branch / PR linked to it. Is this something that is being worked on? Or is it not a feature the team is interested in implementing yet.
@themanmaran Trying to implement transposing at the database level is difficult, since it depends on the database type (we support many). Most people would expect the transposing to be done at data level, so the results can be used elsewhere (or downloaded).
We're currently not working on this, and it will not be a simple thing to implement (unless just visually, which Object Detail will do in upcoming v45).
Yea agreed. Implementing transposing at the database level would be difficult.
I'm happy just to have it done visually. And seems like that's already been solved!
Do you have a link to the 'Object Detail' feature (info/pr/etc.)?
