3 messages in com.mysql.lists.win32Re: percentages and MySQL
FromSent OnAttachments
webm...@w3space.com13 Oct 2001 08:02 
webm...@w3space.com15 Oct 2001 05:36 
webm...@w3space.com15 Oct 2001 07:06 
Subject:Re: percentages and MySQL
From:webm...@w3space.com (webm@w3space.com)
Date:10/15/2001 05:36:58 AM
List:com.mysql.lists.win32

Hi Robert and thanks

it works (adding a : before the =) but how can I execute a second SELECT query using this variable ?

Here's what I have :

SELECT @tot:=COUNT(*) FROM karikter; SELECT pays, COUNT(pays) AS inscrits, FORMAT(((COUNT(pays)/@tot)*100),2) AS pourcentage FROM karikter GROUP BY pays;

and only the first query is executed !!!

I also tried :

SELECT @tot:=COUNT(*), pays, COUNT(pays) AS inscrits, FORMAT(((COUNT(pays)/@tot)*100),2) AS pourcentage FROM karikter GROUP BY pays;

but it isn't correct, tho it works with no error...

Any more idea ?

///// D W A M W 3 S P A C E ///////////////////// Guillaume de Lafontaine - webm@w3space.com . Services Internet : http://www.w3space.com . Annuaire Aquitaine : http://www.aquitanet.com . Espace TeleTravail : http://www.w3workers.com . DWAM.NT Docs & Resources : http://www.dwam.net

-----Message d'origine----- De : Robert Bielecki <robe@hotmail.com> À : webm@w3space.com <webm@w3space.com> Date : lundi 15 octobre 2001 06:03 Objet : Re: percentages and MySQL

Hi,

SELECT @tot:=COUNT(*) FROM karikter ;

----- Original Message ----- From: <webm@w3space.com> To: <win@lists.mysql.com> Sent: Saturday, October 13, 2001 5:02 PM Subject: percentages and MySQL

Hi MySQL users

I'm trying to produce statistics from people recordings... for instance, i want to have all countries listed with the total of subs for each and a proportion in percentage...

SET @tot=3976; SELECT pays, COUNT(pays) AS inscrits, FORMAT(((COUNT(pays)/@tot)*100),2) AS pourcentage FROM karikter GROUP BY pays;

and get

argentina 6 0.15 australia 60 1.51 austria 1 0.03 etc...

which is fine, BUT :

- is there a way to do something like : SET @tot=("SELECT COUNT(*) FROM karikter") not to have to indicate manually the total number of records ?

- is there a better way of calculating percentages ?

- I also had to format the result when using the @tot variable... If I use instead :

SELECT pays, COUNT(pays) AS inscrits, ((COUNT(pays)/3976)*100) AS pourcentage FROM karikter GROUP BY pays;

then my results are automatically formatted the way I want... Is there a reason for this on using variables ?

Thanks...