1

I'm trying to get the percentage of german customers inside the customers table here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

I'm using this query:

SELECT cast((cast((SELECT COUNT(*) FROM Customers WHERE Country = "Germany") as DECIMAL(5,2))/cast((SELECT COUNT(*) FROM Customers) as DECIMAL(5,2))) AS DECIMAL(5,2)) AS PercentageGermans;

For some reason, I still get 0 returned, despite casting all the constituents of the termin to decimal 5,2.

I already searched SO on the topic and found this answer: https://stackoverflow.com/a/26537471/8732285

Here, the casting I'm trying to do is done pretty much the same way I do:

cast((select count(random_int) from test) as decimal(7,2))

The cast is being applied to the full subquery. There it works, which can be seen on the fiddle as well: http://sqlfiddle.com/#!15/63252/37

But I don't understand what's going wrong in my case.

GMB
  • 216,147
  • 25
  • 84
  • 135
Narktor
  • 977
  • 14
  • 34
  • I do not get `0`. I get `An unspecified error occurred.` That might because you quoted `Germany` wrong, and the system is looking for a column with that name, not a string? Unless MySQL allows that use of double quotes by default. Or it could be other things. Anyway, the code you quoted doesn't produce the error you quoted on the site you quoted, so please double-check that you posted the latest information in all cases. – underscore_d Jun 18 '20 at 10:06
  • @underscore_d thats strange, when I copypaste my query, I still get the 0, not an unspec error.Oo – Narktor Jun 18 '20 at 10:25

1 Answers1

2

I'm trying to get the percentage of german customers inside the customers table.

I think that you are overcomplating this. I would just phrase your query as:

select avg(country = 'Germany') ratio_germans from customers

This gives you a value between 0 and 1, that represents the ratio of German customers. You can turn it to a percentage by multiplying it by 100. Say you want a percentage with 2 decimal digits, then:

select round(avg(country = 'Germany') * 100, 2) percent_germans from customers
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Wow, thats interesting, thanks! I thought AVG() is used to calculate the for columns of numerical datatype (int, double)? I mean, here mysql basically has to guess that I want divide the number of rows matching the criteria inside the parameter by the number of ALL rows on the table. Hell, I cant find any documentation on this behavior Oo https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_avg Is this specified behavior? I mean, I didnt even know you can just throw a condition into the parameter. I thought it only takes the name of a column Oo – Narktor Jun 18 '20 at 10:24
  • 2
    My guess is it's taking the average of that condition as a boolean 0 or 1. So, for each row, evaluate to 0 or 1, then take the average of that, which will be a ratio between 0 and 1. I have a somewhat guilty fondness for such implicit casting between boolean and numeric types and often find myself missing it in T-SQL. – underscore_d Jun 18 '20 at 10:27