PHP MySql percentage

Question

My question is about percentages, I'm not an expert so I will try to explain in the better possible way.

I have a table with, let say 700 records, in my mysql server, something like this

+-------+---------+----------+-------+
| Name  | country | language | Birth |
+-------+---------+----------+-------+
| Lucy  | UK      | EN       | 1980  |
| Mari  | Canada  | FR       | 1990  |
| Gary  | Canada  | EN       | 1982  |
| Stacy | Jamaica | EN       | 1986  |
| Joao  | Brasil  | PT       | 1984  |
+-------+---------+----------+-------+

So I query all the records that are between 1980 and 1985 and the result will be:

+------+---------+----------+-------+
| Name | country | language | Birth |
+------+---------+----------+-------+
| Lucy | UK      | EN       | 1980  |
| Gary | Canada  | EN       | 1982  |
| Joao | Brasil  | PT       | 1984  |
+------+---------+----------+-------+

and from this result I would like to obtain:

the percentage of appearance of every languages between those years
```
EN = 75% (3 is the total in this case)
PT = 25%
```
the percentage of appearance of every country that is seen in the resulting table
```
UK = 33%
Canada = 33%
Brasil = 33%
```

I mean how can I convert the results in variables to use them in the final function.

How is the % of `EN` 75? Shouldn't it be `EN = 66%, PT = 33%` under #1? — Kermit, Oct 04 '12 at 14:58
I think is better if I get the php solution, I've been trying to implement the solution given below and I can not get the desired results — Andrés Chandía, Oct 04 '12 at 16:44

score 1 · Answer 1 · edited Oct 04 '12 at 14:53

1

This may work, but something along the line of:

set @total_rows = (SELECT COUNT(*) FROM table WHERE Birth between 1980 and 1985);

SELECT language, percentage
FROM (
    SELECT language, concat(count(language)/@total_rows, "%") AS percentage 
    FROM table WHERE Birth between 1980 and 1985
)

edited Oct 04 '12 at 14:53

user2428118

7,935
4
45
72

answered Oct 04 '12 at 14:02

Mike Mackintosh

13,917
6
60
87

Although your query may be right, I think it may be using a lot of resources to process larger amount of data ( 7000, 70 000, 700 000 records ) – tftd Oct 04 '12 at 14:28
Agreed, casted a user defined varaible to do the total query once. Would love to see other, more optimized solutions though. – Mike Mackintosh Oct 04 '12 at 14:47
WOuld you be so kind to give me a clue on how to implement this in php, I haven't been able to do it. – Andrés Chandía Oct 04 '12 at 16:47
@sixeightzero you're doing it right, but MySQL simply is not a database meant to handle complex sql queries - YES IT CAN, but it's slower. I usually tend to separate the queries into a couple instead of using one. It's not "the best" way but it usually works at least 2 times faster. If it was PostgreSQL I wouldn't have such a problem with that query though. – tftd Oct 04 '12 at 20:47

score 0 · Answer 2 · answered Oct 12 '12 at 05:23

I'll answer here as doing SQL in a comment isn't too comfortable.

To have your percentages you just need to take your previous output as a view and add the percentage column, this should do :

select NAME, QUANTITY, QUANTITY/(select count(1) from TABLE) as PERCENTAGE
from (
    select NAME, count(1) as QUANTITY
    from TABLE
    where condition
    group by NAME
)

But this is not really optimized since you call a count on whole table everytime you wanna fetch a single row, and even if MySQL will index results of count, it still won't be the best.

What you should do is to first get the total size of the table into your php :

select count(1) as totalsize
from TABLE

Then to use it in your next request :

select NAME, QUANTITY, QUANTITY/[totalsize from php] as PERCENTAGE
from (
    select NAME, count(1) as QUANTITY
    from TABLE
    where condition
    group by NAME
)

I need some further help (as always). The first query (totalsize) do I have to put it like this in my php? $query1 = "select count(1) as totalsize from TABLE";. While the second query should be like this: $query2 = "select NAME ......."; — Andrés Chandía, Oct 12 '12 at 20:07
2nd question: the php in [totalsize from php] do I have to replace it for something? — Andrés Chandía, Oct 12 '12 at 20:09

PHP MySql percentage

2 Answers2

Linked