Custom Questions

I want to find the results of this query by using a custom question. I don’t want to use a native query. can someone tell me the steps that I need to take in order to achieve this.

SELECT to_char(Date, ‘YYYY-MM’) as Month, sum(order_value)/count(distinct(Order_Number)) as avg_order_ value FROM mainorders
group by to_char(Date, ‘YYYY-MM’)
order by to_char(Date, ‘YYYY-MM’);

Hi @avishbhasin
When you want to make a calculation, then use the Custom Expression.
Learn more here: https://www.metabase.com/blog/custom-expressions/index.html

when I write the custom expression, I don’t get an option to divide by ‘Order_number’. The order number contains a numerical order number given to each order and it repeats in various rows.

@avishbhasin Post “Diagnostic Info” from Admin > Troubleshooting, and which database you are querying.
And what is the Field Type of order_number in Admin > Data Model > (database) > (table) > (column) ?

The field type is ‘No Special Type’

{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.111 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.8+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.8”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.8+10”,
“os.name”: “Linux”,
“os.version”: “4.4.0-1113-aws”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-09-15”,
“tag”: “v0.36.6”,
“branch”: “release-0.36.x”,
“hash”: “cb258fb”
},
“settings”: {
“report-timezone”: “Asia/Calcutta”
}
}
}

@avishbhasin

  1. You should migrate away from H2 if you’re using Metabase in production:
    https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
  2. Go to Admin > Data Model > (database) > (table) > order_number :gear:, then your URL should look like /admin/datamodel/database/4/table/11/85/general, where 85 is the field ID, then change the URL to /api/field/85 and post the output here (redact where needed)

could you elaborate the second point

@avishbhasin What is the field ID of Order_number? When you have that id, then change the url to /api/field/<the-id>, which will give detailed information about the field.

this is the url :http://13.127.91.217:3300/admin/datamodel/database/2/table/11/70/general
What should be the replaced url now?

@avishbhasin http://13.127.91.217:3300/api/field/70

{“description”:null,“database_type”:“varchar”,“table_id”:11,“table”:{“description”:null,“entity_type”:“entity/TransactionTable”,“schema”:“public”,“db”:{“description”:null,“features”:[“full-join”,“basic-aggregations”,“standard-deviation-aggregations”,“expression-aggregations”,“percentile-aggregations”,“foreign-keys”,“right-join”,“left-join”,“native-parameters”,“nested-queries”,“expressions”,“set-timezone”,“regex”,“case-sensitivity-string-filter-options”,“binning”,“inner-join”,“advanced-math-expressions”],“cache_field_values_schedule”:“0 0 0 * * ? ",“timezone”:“Asia/Jerusalem”,“auto_run_queries”:true,“metadata_sync_schedule”:"0 0 * * * ? ",“name”:“Max”,“caveats”:null,“is_full_sync”:true,“updated_at”:“2020-10-08T09:00:00.03”,“details”:{“host”:"",“port”:,“dbname”:“",“user”:"”,“password”:"",“ssl”:,“additional-options”:null,“tunnel-enabled”:false},“is_sample”:false,“id”:2,“is_on_demand”:false,“options”:null,“engine”:“postgres”,“created_at”:“2020-10-06T05:50:58.406”,“points_of_interest”:null},“show_in_getting_started”:false,“name”:"**”,“caveats”:null,“updated_at”:“2020-11-02T08:10:07.471”,“entity_name”:null,“active”:true,“id”:11,“db_id”:2,“visibility_type”:null,“field_order”:“database”,“display_name”:"*****",“created_at”:“2020-10-06T05:50:58.684”,“points_of_interest”:null},“special_type”:“type/Description”,“name”:“order_number”,“fingerprint_version”:4,“has_field_values”:“search”,“settings”:null,“caveats”:null,“fk_target_field_id”:null,“dimensions”:[],“updated_at”:“2020-11-07T05:34:18.102”,“custom_position”:0,“active”:true,“parent_id”:null,“id”:70,“last_analyzed”:“2020-10-06T05:51:21.985”,“position”:3,“visibility_type”:“normal”,“preview_display”:true,“display_name”:“order_number”,“database_position”:4,“name_field”:null,“fingerprint”:{“global”:{“distinct-count”:1130,“nil%”:0.0},“type”:{“type/Text”:{“percent-json”:0.0,“percent-url”:0.0,“percent-email”:0.0,“average-length”:8.0}}},“created_at”:“2020-10-06T05:50:58.873”,“base_type”:“type/Text”,“points_of_interest”:null}

@avishbhasin Okay, so the column order_number is not numeric, but is a VARCHAR. You have to manually input the column name, so the Custom Expression would look like this:
Sum([order_value])/Distinct([order_number])
You’re seeing this issue:
https://github.com/metabase/metabase/issues/13469 - upvote by clicking :+1: on the first post