Page 1 of 1

how do Generating Database Cross Tabulations in php

Posted: Thu Jul 23, 2009 6:45 am
by manojsemwal1
hai everybody iam facing a problem to print my html table
My query are like
$sql2="SELECT Distinct
country, loc AS location,
COUNT(CASE WHEN dept = 'pers' AND gender = 'f' THEN id ELSE NULL END)
AS 'pers-f',
COUNT(CASE WHEN dept = 'pers' AND gender = 'm' THEN id ELSE NULL END)
AS 'pers-m',
COUNT(CASE WHEN dept = 'sales' AND gender = 'f' THEN id ELSE NULL END)
AS 'sales-f',
COUNT(CASE WHEN dept = 'sales' AND gender = 'm' THEN id ELSE NULL END)
AS 'sales-m',
COUNT(CASE WHEN dept = 'dev' AND gender = 'f' THEN id ELSE NULL END)
AS 'dev-f',
COUNT(CASE WHEN dept = 'dev' AND gender = 'm' THEN id ELSE NULL END)
AS 'dev-m',
COUNT(*) AS total
FROM
person
INNER JOIN
depts ON (person.dept_id=depts.dept_id)
INNER JOIN
location ON (location.loc_id=person.loc_id)
INNER JOIN
countries ON (location.country_id=countries.country_id)
GROUP BY
country, location";

And Result like this:

country location f -m f m f m total
Germany Berlin 1 0 0 0 0 1 2
Germany Bonn 0 0 0 1 0 0 1
Germany Munich 0 1 1 0 0 0 2
Itly Rome 0 1 0 0 1 0 2
uk London 0 1 0 1 0 0 2


When to get output in html format how can i print Country Name only Once time.

with regards,
Manoj

Re: how do Generating Database Cross Tabulations in php

Posted: Thu Jul 23, 2009 6:48 am
by jackpf
You can select DISTINCT country name.

Re: how do Generating Database Cross Tabulations in php

Posted: Thu Jul 23, 2009 7:00 am
by manojsemwal1
Thanks for your quick reply i tried distinct but still display same result.

Re: how do Generating Database Cross Tabulations in php

Posted: Thu Jul 23, 2009 7:43 am
by manojsemwal1
somebody sent some reply