我想根据这两个表显示这样的摘要

Question

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";

Answer 1

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