How to bucket the data?

#1

My apologies for my French having trouble translating properly here.

I have a question listing how many rows of a given table each customer have.
I'd like to have a visualization showing the results in buckets.
Say, 1 to 5, 6 to 20, 21 to 40 etc.

How do I tailor my question to do something like this?

Thank you for your time,

Antoine

#2

Hi @Antoine
There's various terms for it, but it's generally called "binning" in Metabase.
You can set that, when you a grouping by a field:
https://www.metabase.com/docs/latest/users-guide/04-asking-questions.html#grouping-your-metrics

1 Like
#3

Bonjour Flamber,

In my case, I'm doing a count by database_name and this lists how many rows per database_name, or customer actually in my case since each customer has their own db.

I don't have an option to autobin database_name so as to group the results in various bin sizes.

#4

@Antoine I think my brain is a little slow today. Don't think I can even visualize what you're trying to do.
But yes, you cannot do binning on a string field. If you can show how you would have done it in SQL, then it's easier to understand if it can be done in GUI.

#5

Morning Flamber,

That I confuse highly intelligent people doesn't bode well on my part I'm afraid.
My apologies, let me unpack as best as I can :slight_smile:

The result of my count is I have a list like this:

Count%20by%20database_name
Count by database_name.png1987×726 105 KB

Customer Count (number of rows in a table)
A <------> 1
B <------> 1
C <------> 2
D <------> 5
E <------> 7
F <------> 7

What I'd like is to be able to group these into buckets or bins based on the count seen above.

Something like

Count 1 to 5 <--------> 4 customers
Count 6 to 10 <------> 2 customers

thank you for your help,

Antoine

#6

@Antoine I'm just hitting a keyboard a lot of times. Doesn't make me intelligent :wink:

Okay, now I understand, I think - it's been a long couple of days.
You have to do a slight side-step because you currently cannot do binning on a double aggregation in the same question.

  1. So first you create a question - Count grouped by DatabaseName - save that question:
    image
    image.png1920×582 31.4 KB
  2. Then you create a new question, but select the Saved Question as the base:
    image
    image.png1920×577 32.7 KB
  3. It's now doing binning:
    image
    image.png1920×971 35 KB

By the way, I can see you're using a fairly old version of Metabase. I would advise you to look into upgrading at some point. Latest release is 0.43.0, with 0.43.1 coming next week.
https://github.com/metabase/metabase/releases

1 Like
#7

Thank you Flamber,

I got to bin them automatically.
I've let IT know about your version comment as well.

#8

Flamber, bonjour :slight_smile:
I'm told we are on metabase cloud and updated regularly.
Could you confirm that as I'm wondering why we would not be on the latest in that case.

Thank you again for your wonderful support.
I'm building a dashboard for folks here and it'll be very valuable and couldn't have been done without your support.

Cheers from Vancouver,

Antoine

#9

@Antoine Okay, might be, think I was tired - looking at too many different errors throughout the day.
If you are on Metabase Cloud, then you are on 1.42.4 and going to be upgraded to 1.43.1 very soon.