I’m running a query to summarize a field/column in a table. The binning options however are rigid, and of uniform widths.
How could I output/customize the bins to show counts:
bin 1: 0-99 items
bin 2: 100-399 items
bin 3: > 400 items
Sadly, I’m not good at SQL so though I tried to read posts and search on the web, didn’t find anything I could use.
There’s a request open about that:
https://github.com/metabase/metabase/issues/12847 - upvote by clicking on the first post
But what you’re asking for would be possible to do with
case function in Custom Expressions:
Hi @Cali_Claudster, hope you find it helpful.
Hi MollyZ, I’m SUCH a noob…
I tried to input the case command(s) but kept getting an error (syntax)
here’s the original short query
and where I tried to enter the case argument
If you have time to show me what I’m entering incorrectly?
Hi flamber, I tried to upvote, but that’s a different system? my discourse credentials weren’t recognized - - do I have to re-sign up?
@Cali_Claudster Our forum is not the same as GitHub - or any other site.
Instead of doing it in SQL, you can just use Custom Expression, but you’re missing commas between the columns in your SELECT-clause.
@flamber Thanks for the info, I’ll create an account there so I can upvote that ticket.
I didn’t quite understand your observation about missing commas?
this was the custom question: (the SQL was autogenerated by Metabase)
sidedoor_net - -500.0) / 250.0)) * 250.0) + -500.0) AS
sidedoor_net, count(*) AS
show_month >= date “2021-01-01”
show_month < date “2021-02-01”)
If I’m to use Custom expression from the question screens, what would I write in order to get bins of 0-99, 100-399, and 400+?
This is how you could do it with Custom Expressions - example with Sample Dataset:
case([Quantity] < 10, "small", [Quantity] < 50, "medium", "large")
This is where you’re missing commas in your SQL.
@flamber we must have different versions of metabase… I don’t see the custom columns option on mine, just filter and summarize. (you can see it’s Data, then filter, then summarize as opposed to yours which shows “custom column”):
I did try and go into the SQL, and I get close, but now I see that it’s summing up the values, not counting the “rows”:
What I’m trying to show is the number of shows/rows where the net was within each of those custom bins, however what I’m actually showing, is binning them appropriately, but visualizing the sum, vs the count.
I’m really wishing I’d taken some sort of SQL course
@Cali_Claudster Could it be that you’re BigQuery? Custom Column is currently not available:
https://github.com/metabase/metabase/issues/9382 - upvote by clicking on the first post
Doing this should work:
SELECT `source`.`MyBinning`, count(*) AS `count`
FROM (SELECT CASE WHEN `sidedoor_net` < 100 THEN '0-99' WHEN `sidedoor_net` < 400 THEN '100-399' ELSE '400 or greater' END AS `MyBinning` FROM `analytics_prod.settlements`) `source`
GROUP BY `source`.`MyBinning`
ORDER BY `source`.`MyBinning` ASC
I never use quotation marks for referring to a column or dataset. But if that works for you, then could you please try this:
CASE WHEN sidedoor_net BETWEEN 0 AND 99 THEN ‘0-99’
WHEN sidedoor_net BETWEEN 100 AND 399 THEN ‘100-399’
WHEN sidedoor_net > 400 THEN ‘Greater than 400’
END AS ‘sidedoor_net’, –you need to put commas to separate your columns
COUNT(*) AS ‘count’