Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: PHP5 - Complex query...

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Germaris
    How should I modify my query?
    change this --
    Code:
     CONCAT( ((u.n / l.n) * 100 )
    to this --
    Code:
     u.n AS u_n, l.n AS l_n
    and do the calculation of the percentage, along with the cosmetic manipulation of decimal positions and concatenation of percent sign, in php

    Last edited by r937; 05-22-09 at 07:24.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    Waow! Yes, I understand.
    This is great.

    Thank you very much for your reply!

    Best regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •