sum case when

[vengelus82]

Hi guys,

I am having problem to add additional condition(s) to my SQL statement in PHP

This is the following SQL statement:
SELECT EXTRACT(YEAR FROM users.date) AS Year,
SUM(CASE WHEN table_gender.profile_id = 1 THEN 1 ELSE 0 END) AS "Men",
SUM(CASE WHEN table_gender.profile_id = 2 THEN 1 ELSE 0 END) AS "Women",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 30 THEN 1 ELSE 0 END) AS "Men < 18 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 31 THEN 1 ELSE 0 END) AS "Men 18 - 24 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 32 THEN 1 ELSE 0 END) AS "Men 25 - 34 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 33 THEN 1 ELSE 0 END) AS "Men 35 - 44 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 35 THEN 1 ELSE 0 END) AS "Men 45 - 54 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 36 THEN 1 ELSE 0 END) AS "Men 55 - 64 years",
SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 37 THEN 1 ELSE 0 END) AS "Men 65+ years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 30 THEN 1 ELSE 0 END) AS "Women < 18 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 31 THEN 1 ELSE 0 END) AS "Women 18 - 24 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 32 THEN 1 ELSE 0 END) AS "Women 25 - 34 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 33 THEN 1 ELSE 0 END) AS "Women 35 - 44 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 35 THEN 1 ELSE 0 END) AS "Women 45 - 54 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 36 THEN 1 ELSE 0 END) AS "Women 55 - 64 years",
SUM(CASE WHEN table_gender.profile_id = 2 AND table_birth.profile_id = 37 THEN 1 ELSE 0 END) AS "Women 65+ years"
FROM users
INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON users.id = table_birth.user_id
WHERE table_gender.profile_id IN (1,2) AND table_birth.profile_id IN (30,31,32,33,35,36,37)
GROUP BY EXTRACT(YEAR FROM users.date)

The first three conditions is defined in the following php:

<?php
include 'db.class.php';
$db = new DB($base, $server, $user, $pass);
$sql = <<< EOQ
SELECT EXTRACT(YEAR FROM users.date) AS `year`, table_gender.profile_id AS `gender`, table_birth.profile_id AS `age`
FROM users
INNER JOIN user_profile AS table_gender ON users.id = table_gender.user_id
INNER JOIN user_profile AS table_birth ON users.id = table_birth.user_id
WHERE table_gender.profile_id IN (1,2)
AND table_birth.profile_id IN (30,31,32,33,35,36,37)
ORDER BY EXTRACT(YEAR FROM users.date)
EOQ;
$data = array();


$result = $db->query($sql);
while( $row = $db->fetchNextObject() ) {
if( !isset($data[$row->year]) )
$data[$row->year] = array(
'm' => 0,
'v' => 0
);
switch($row->gender) {
case 1:
$data[$row->year]['m']++;
break;
case 2:
$data[$row->year]['v']++;
break;
}
}

echo "<table border=1 >";
echo "<tr><th>Year</th><th>Male</th><th>Female</th></tr>";
foreach($data as $year => $_data) {
echo "<tr>";
echo "<td>{$year}</td><td>{$_data['m']}</td><td>{$_data['v']}</td>";
echo "</tr>";
}
echo "</table>";

but now I would like to add the other statements, but really don't know how...not a particular programmer..

My question is how to add

SUM(CASE WHEN table_gender.profile_id = 1 AND table_birth.profile_id = 30 THEN 1 ELSE 0 END)

to my php?

p/s: know that the login info isn't in it, but that is private