My two MYSQL tables are as follows:
Table 1 : citizen
=============================
ID | Name | Sex | Address |
=============================
5 | James | Male | India
6 | Shella|Female | India
7 | Jan | Male | NY
8 | May | Female | USA
==============================
Table 2: benefits
==========================
ID| citizen_ID | benefits
==========================
1 | 5 | SSS
2 | 6 | Coco Life
3 | 7 | SSS
4 | 7 | Sunlife
==========================
I want to display that looks like this:
====================================================================
Address | Total Citizen | Male | Female | SSS | Coco Life | Others |
====================================================================
India | 2 | 1 | 1 | 1 | 1 | 0 |
NY | 1 | 1 | 0 | 1 | 0 | 1 |
USA | 1 | 0 | 1 | 0 | 0 | 0 |
==================================================================
Anybody can give me a hint on how to do this? My initial code:
$sql = "SELECT Address,
count(case when Sex='Male' then 1 end) as male_cnt,
count(case when Sex='Female' then 1 end) as female_cnt,
count(*) as total_cnt FROM citizen
GROUP BY Address";
You are on the right track. You now simply need to do a Left Join from the Address table to the benefits table. Left join will allows us to consider a Address even when there is no corresponding benefits entry for any of its citizens.
In order to count total citizens, male count and female count, you now need to use COUNT(DISTINCT ID) after the join. As Joining may create duplicate rows, as a citizen may have more than one benefits.
Also, in order to count "Other" benefits, we need to ensure that the benefit IS NOT NULL and it is NOT IN ('SSS', 'Coco Life').
In multi-table queries, it is advisable to use Aliasing for Code clarity (readability) and avoiding ambiguous behaviour.
SELECT
c.Address,
COUNT(DISTINCT CASE WHEN c.Sex = 'Male' THEN c.ID END) AS male_cnt,
COUNT(DISTINCT CASE WHEN c.Sex = 'Female' THEN c.ID END) AS female_cnt,
COUNT(DISTINCT c.ID) AS total_citizen_cnt,
COUNT(CASE WHEN b.benefits = 'SSS' THEN 1 END) AS SSS_cnt,
COUNT(CASE WHEN b.benefits = 'Coco Life' THEN 1 END) AS Coco_Life_cnt,
COUNT(CASE WHEN b.benefits IS NOT NULL AND
b.benefits NOT IN ('SSS', 'Coco Life') THEN 1 END) AS Others_cnt
FROM citizen AS c
LEFT JOIN benefits AS b
ON b.citizen_ID = c.ID
GROUP BY c.Address
After countless hours of testing, getting help from a cat in code writing, singing shaman songs and ect... I think i made an exapmle that gives desired output. I tested it a bit, and it seems to work.
However, no matter what i did SQL alone was not enogh to get the job done. And in desperation, I had to resort to using php for realization. And even using php it proved to be a challange.
Below I am pasting my take on this problem, using mainly php. I only hope that it may help some one or give someone good idea about refactoring the code and making logic easier.
<?php
//Connect to DB
$mysql = new mysqli("127.0.0.1", "root", "", "test");
if ($mysql->connect_error) {
die("Connection failed: " . $mysql->connect_error);
}
/*Simplified query - we only get id, address, sex and benefits from our two tabels.
LEFT JOIN is necessary, because we do not want to miss citizenz that have no benefits.
*/
$sql_statement = "select citizen.ID, citizen.Address, citizen.Sex, benefits.benefits from citizen left join benefits on citizen.ID=benefits.citizen_id";
$results = $mysql->query($sql_statement);
if(!$results)
{
die("Result is empty! Check the query or data inside tables");
}
/*
* Here we initialize some variables to keep track of further calculations
*
* @total_women - array($counry => $amount) - Total number of women, divided by countries, and number of men in each country
* @total_men - array($counry => $amount) - Total number of men, divided by countries, and number of men in each country
* @total_benefits - array($counry => array($benefit => $amount)) - Total number of benefits, divided by countries, benefit types and number of benefits in each country
* @ids - array() - Citizen id's from DB. We have to keep track of them, in order to avoid "extra" people in outr results
* @Addresses - array() - Addresses from DB. We have to keep track of them to avoid duplicate addresses in our results
* @$benefit_names() - array() - Keeps the names of benefits, whick we want to count seperatly, All other benefits are "Others"
*/
$total_women = array();
$total_men = array();
$total_benefits = array();
$ids = array();
$Addresses = array();
$benefit_names=array('SSS', 'Coco Life');
//Iterating over recieved results from DB
foreach($results as $result) {
//Getting all necessary data
$id = $result['ID'];
$address = $result['Address'];
$sex = $result['Sex'];
$benefit = $result['benefits'];
//Ensuring that we don't get duplicate Addresses
if(!in_array($address, $Addresses)) {
array_push($Addresses, $address);
}
//Ensuring we don't get extra people
if(!in_array($id, $ids))
{
array_push($ids, $id);
if($sex=='Male') {
//Dividing men by address
if(array_key_exists($address, $total_men))
{
$total_men[$address]++;
}
else
{
$total_men[$address] = 1;
}
}
else if($sex=='Female') {
//Dividing women by address
if(array_key_exists($address, $total_women))
{
$total_women[$address]++;
}
else
{
$total_women[$address] = 1;
}
}
}
//Ensuring a person has a benefit
if($benefit) {
//Dividing benefits by address
if(array_key_exists($address, $total_benefits)) {
//Dividing benefits by benefit name.
if(in_array($benefit, $benefit_names))
{
if(array_key_exists($benefit, $total_benefits[$address])) {
$total_benefits[$address][$benefit]++;
}
else {
$total_benefits[$address][$benefit]=1;
}
}
else if(array_key_exists('Others', $total_benefits[$address]))
{
$total_benefits[$address]['Others']++;
}
else {
$total_benefits[$address]['Others'] = 1;
}
}
else {
if(in_array($benefit, $benefit_names)) {
$total_benefits[$address][$benefit] = 1;
}
else {
$total_benefits[$address]['Others'] = 1;
}
}
}
}
//Here after all the manipulations, our hash map looks ugly, to make your life eaier later we fill up the missing parts with 0
foreach($Addresses as $address) {
foreach($benefit_names as $name) {
if(!isset($total_benefits[$address][$name])) {
$total_benefits[$address][$name]=0;
}
}
if(!isset($total_benefits[$address]['Others'])) {
$total_benefits[$address]['Others']=0;
}
}
/*
* At this point all the neseccary calculations are made. We only have to take care of outputting the data.
*/
?>
<html>
<body>
<table>
<tr>
<th>Counry</th>
<th>Total citizen</th>
<th>Male</th>
<th>Female</th>
<?php
//Here we make sure that all our separate benefit names get theit own column.
foreach($benefit_names as $benefit) { ?>
<th><?php echo $benefit; ?></th>
<?php }
//After we displayed all our separate benefits, we display the "Others" column
?>
<th>Others</th>
</tr>
<?php
$temp;
//Here we go through all the unique addresses that we met earlier, ad make a roww for each of them
foreach($Addresses as $address) { ?>
<tr>
<td><?php echo $address; //Outputting current address?></td>
<td>
<?php
//Here we take care of calculating total citizens based on our current address and output them
$total_citizen = 0;
if(array_key_exists($address, $total_men)) $total_citizen+=$total_men[$address];
if(array_key_exists($address, $total_women)) $total_citizen+=$total_women[$address];
echo $total_citizen;
?>
</td>
<!--Here we display number of women, based on our corrent address-->
<td><?php if(array_key_exists($address, $total_men)) echo $total_men[$address]; else echo "0"; ?></td>
<!--Here we display number of men, based on our corrent address-->
<td><?php if(array_key_exists($address, $total_women)) echo $total_women[$address]; else echo "0"; ?></td>
<?php
/*
* Here is probably the maddest piece of php code.
* We have to make sure that the layout of our data values corresponds with our Column headers layout.
* For that, we first iterate over our separate benefit names.
* By doing this we ensure that all our seperate benefit names are filled with some data
* After we filled all seperate benefits are filled, we make sure that "Others" column is also filled with data
*/
foreach($benefit_names as $benefit) {
//Here we have to make sure that there were some benefits. If there were no benefits, then we fill them with 0.
if($total_benefits) { ?>
<td><? echo $total_benefits[$address][$benefit]; ?>
<?php } else { ?>
<td>0</td>
<?php }
}
//Again, have to make sure that there were some benefits. If there were no benefits, then we fill them with 0.
if($total_benefits) { ?>
<td><?php echo $total_benefits[$address]['Others'] ?></td>
<?php } else { ?>
<td>0<td/>
<?php } ?>
</tr>
<?php } ?>
</table>
<body>
</html>
I placed comments as much as I could, in order to make it easier to understand, what this piece of code does.