Error calculating percentage from two Summarizes

Get Help
1

I have a question that groups on two columns in a table (MySQL) and gets a simple row count. It also has a Sum calculation of a custom column that uses a Case() to return 0 or 1 based on a condition. What I'm trying to do is get the percentage that the Sum of the Case() is of the total Count(). My custom column with the Case() is called "Qualified", and my percentage Summarize expression looks like this:

Sum([Qualified]) / Count()

When I attempt to visualize the question, I get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'double) / NULLIF(CAST(COUNT(*) AS double), 0.0) AS QualifiedPercentage FROM (S' at line 2

If I look at the SQL generated by the question, I see that it attempts to cast both numbers to double and check for a null on the Count(). If I copy the generated SQL and directly query the database, it fails with the same error. If I remove the CASTs from the SQL, it runs correctly.

How can I instruct Metabase not to CAST?

2

What database and version are you running the query against?

1 Like
3

I'm running against MySQL 5.7.

4

Metabase no longer supports MySQL 5.7.x, and neither does anyone else. Far past time to put that old pony out to pasture. Get the performance and feature improvements in 8.x and live a better life (you don't know what you're missing until you get window functions).

2 Likes
5

Yeah, I know. I've been dying for Common Table Expressions. But it's not my call, so I'm limping along on this thing. Now that I Google a bit, I see that cast as double isn't supported in MySQL 5.7. Bummer!

Thanks for your help.

6

Maybe this is the straw that breaks that camel's back and gets your company to spend the 2 whole hours doing the upgrade. :slight_smile:

The breaking change is in Metabase 55, so if you can roll back to 54 you can hang on for a bit, but clearly this isn't a maintainable situation.

1 Like