>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.