[thelist] Better way to do this (SQL)?

[John.Brooking at sappi.com]

>Date: Tue, 18 May 2004 11:42:08 +0300
>From: Burhan Khalid <thelist at meidomus.com>
>To: thelist at lists.evolt.org
>Subject: [thelist] Better way to do this (SQL)?
>
><snip>
>
>Basically, I would like to show:
>
>total headcounts and percentages for 'male' and 'female' respondents 
>(database column 'sex')"
>
><snip>
>
>But I'm wondering if there is a way to do this using one query?

I don't know PHP syntax, but you would have to use a recordset to loop over
multiple result rows. As to the query, if we didn't have to get the
percentage, it would be easy:

   SELECT sex, COUNT(*)
   FROM table
   GROUP BY sex
   
Even if you're not familiar with SQL, it should be easy to figure out what
this does. The "GROUP BY" means summarize the data for the same values of
sex, so then the SELECT simply shows each value of sex once, with its count,
for example:

      F  14
      M  22

Now adding the percentage is a bit tricky. On each row, you need to divide
by the total. To cut to the chase, the following works on my Oracle DB, I
don't know if other DB's support this syntax:

   SELECT S.sex, COUNT(*), T.total, COUNT(*) / T.total * 100
   FROM table S
      , ( SELECT COUNT(*) AS total FROM table ) T
   GROUP BY S.sex, T.total

   Results:   
      F   14   36   38.8888888
      M   22   36   61.1111111
      
"Table" T is a subselect returning one number, the total number of
respondents. This is then joined with the normal table, so that the total
appears on every row (which I have also included in the output just for the
sake of this example). Note the lack of join conditions, unnecessary since T
has only one row. The percentage is then a simple Count/Total formula,
multiplied by 100 to express as a whole number percentage. (You can also
apply your DB's rounding function if you wish.) Lastly, note that we also
need to add the total field to the GROUP BY, else the DB will complain about
the formula field.

If your DB does not support a subselect as a table spec, perhaps you could
define a view or something to get around that.

Hope this helps!

John Brooking, Application Developer
Sappi Fine Paper
-- 
 

This message may contain information which is private, privileged or
confidential and is intended solely for the use of the individual or entity
named in the message. If you are not the intended recipient of this message,
please notify the sender thereof and destroy / delete the message. Neither
the sender nor Sappi Limited (including its subsidiaries and associated
companies) shall incur any liability resulting directly or indirectly from
accessing any of the attached files which may contain a virus or the like.