CODEWITHSUNDEEP
mysql
SuperDJ
SuperDJ

Reputation: 7661

Calculate percentage with MySQL

I have the following table structures

Languages:

id  |  language
1   |  English
2   |  Dutch

Translations:

id | translation_id | translation | language_id
1  |                | Username    | 1
2  |                | Password    | 1
3  | 2              | Wachtwoord  | 2

I tried the following query to get an overview for each language:

SELECT
  `languages`.`id`,
  `language`
  concat( round( ( COUNT(`translations`.`languages_id` = 1) / COUNT(`translations`.`languages_id` = 2)) * 100, 0), '%') AS `percentage`
FROM `languages`
JOIN `translations`
ON  `languages`.`id` = `translations`.`languages_id`

But for some reason I'm only getting 100% for percentage. While I would expect to get 50% for Dutch.

The result I get right now is just 

[id] => 1 [language] => English [percentage] => 100%

While I would expect to get: 

[id] => 1 [language] => English [percentage] => 100%
[id] => 2 [language] => Dutch [percentage] => 50%

I know I have no translations linked for English.

In this case English is the default language so that would be 100% every other language is a certain percentage of English. That's why I expect Dutch to be 50%.

Also wouldn't it be possible to get the percentage for every language without doing the same query for every language? Just like when only selecting the id and language you would get both 2, Dutch and 1, English rows returned.

Upvotes: 0

Views: 1416

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can find count of rows for each language and divide it with count of english language like this:

Try this:

select l.*, concat(round(100 * count(t.languages_id) / t2.cnt,0),'%')
from languages l
left join translations t
on l.id = t.languages_id
cross join (
    select count(*) cnt
    from translations
    where languages_id = 1
) t2
group by l.id;

Upvotes: 2

Related Questions