@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")
@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"):
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
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’
…