Hi there!

I manage a directory of former students of about forty schools.

The following query (in a PHP 5 script):

SELECT u.school, CONCAT( ((u.n / l.n) * 100 ) , ' %') AS ratio FROM (SELECT school, COUNT( * ) AS n FROM _a_users GROUP BY list HAVING ( n > 0 ) ORDER BY n ASC) AS u LEFT JOIN (SELECT school, COUNT( * ) AS n FROM _a_list GROUP BY list HAVING ( n > 0 ) ORDER BY n ASC) AS l ON l.school = u.school ORDER BY school ASC;

gives me the following result:

Aix-en-Provence 9.6480 %
Autun 13.2667 %
Billom 4.5960 %
Bingerville 12.1065 %
Brazzaville 2.3810 %
Brest (C.I.N.) 71.4286 %
Brest (Lycée naval) 1.4114 %
Brest (Maistrance) 77.7778 %
Brest (Pupilles, Mousses et Pont) 22.6573 %
Dalat 1.2945 %
Épinal 0.8368 %
Fianarantsoa 12.5000 %
Grenoble 16.4675 %
Hammam-Righa 5.2632 %
Issoire 7.8942 %
Koléa 11.5646 %
La Boissière 4.5303 %
La Flèche 38.3220 %
Le Mans 13.5260 %
Le Tampon 2.0761 %
Les Andelys 8.8643 %
Montélimar 2.8818 %
Niamey 40.0000 %
Ouagadougou 27.2727 %
Rochefort 90.9091 %
Rochefort (Aéronavale) 69.2308 %
Rochefort - Martrou 83.3333 %
Saint Cyr-l'École 14.4187 %
Saint Hippolyte-du-Fort 0.4545 %
Saint-Louis 11.6766 %
Saint-Maixent-l'École 11.7647 %
Saint-Mandrier 7.1640 %
Saintes 16.8217 %
Tulle 7.1373 %


The list shows for each school the ratio between the registered users and the students listed in two different tables:
_a_users and _a_list

I want to have:
- only two decimal numbers after the dot
- only the numbers before the dot for numbers having no decimals (like "Niamey" in the above list)

How should I modify my query?

Many thanks in advance for your help!

Best regards,

Gerard

change this --to this --and do the calculation of the percentage, along with the cosmetic manipulation of decimal positions and concatenation of percent sign, in php

Waow! Yes, I understand.
This is great.

Thank you very much for your reply!

Best regards.