Adjusting Bin sizes

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.

Hi @Cali_Claudster
There’s a request open about that:
https://github.com/metabase/metabase/issues/12847 - upvote by clicking :+1: on the first post
But what you’re asking for would be possible to do with case function in Custom Expressions:
https://www.metabase.com/learn/building-analytics/notebook-editor/custom-expressions.html

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)

SELECT ((floor(((analytics_prod.settlements.sidedoor_net - -500.0) / 250.0)) * 250.0) + -500.0) AS sidedoor_net, count(*) AS count
FROM analytics_prod.settlements
WHERE (analytics_prod.settlements.show_month >= date “2021-01-01”
AND analytics_prod.settlements.show_month < date “2021-02-01”)
GROUP BY sidedoor_net
ORDER BY sidedoor_net ASC

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+?

@Cali_Claudster
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 :man_facepalming:

@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 :+1: 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:
SELECT
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’