Odtnhj

My 3 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| benefits
==========================
1 | SSS
2 | Coco Life
3 | PhiHealth
4 | Sunlife
==========================

Table 3: pensioners

============================
ID| benefits_ID | citizen_ID
============================
1 | 1 | 5
2 | 2 | 6
3 | 1 | 7
4 | 4 | 7
==========================

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?

You can do a Left Join from the Address table to the benefits table, via pensioners table, using the appropriate relationships. 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 pensioners AS p
 ON p.citizen_ID = c.ID 
LEFT JOIN benefits AS b 
 ON b.ID = p.benefits_ID 
GROUP BY c.Address