So we have a metric where we HAVE use sql to group our calculations by order id as well as customer, we then take that metric and use metabase’s built in metrics creator to do sum of 1st calculation and sum of second calculation group by customer where we end up with a table with the customer and the 2 sums we need. When we try to sort the data by either sum1 or sum2 we get the error “sum” is ambiguous, is there any way around this so we do not have to have a users manually sort the metric everytime they view it?
The answer could be in the title of your post … if you combine it with the info in the last bullet point under “note” in this part of the user guide: https://www.metabase.com/docs/latest/users-guide/04-asking-questions.html#using-saved-questions-as-source-data
Try naming one of your two sums, sum1 and sum2, differently - so the are less ambiguous.
Please share if that does it?
To my knowledge i am unable to change the name of sum. When i do sum of in metabase it names that column sum, sum1, sum2 and so on.
Ah yeah, bugger, gotcha!
I think I have once been bitten by the same, and was able to work around it by not picking
sum() directly but specifying it as a custom calculation which you get to name yourself. Can’t remember if I had to trick it by doing some fake calc on top of it (add and subtract something) so it didn’t see through me.
Start off by not wrapping it as a metric, then maybe add that later if the workaround can get you through it.
The related GitHub issue discussion - https://github.com/metabase/metabase/issues/6042
We are unable to do custom expressions as we need to do sql queries to calculate the data a specific way. (If we do sum in sql we do not get the correct calculations)
Not knowing the specifics of what you need to do in SQL, would it maybe be an option to push the SQL down into a database view? (Guess this is my last shot for trying to give a workaround, for now …)