php - 从MySql的两个表中计算带有0的值

Question

我有 2 个表，即“联系人组”和“联系人订阅者”，我们正在尝试加入它们并对其进行类似值的计数。我需要为没有任何值的相应字段设置值“0”。表格示例如下:

Contact Group Table:

id | name      | Hash | 

1  | January   | asld | 
2  | February  | asld | 
3  | March     | asld | 
4  | April     | asld | 
5  | May       | asld | 

Contact Subscriber Table:

id | name            | group_id | subscribed at |  unsubscribed at |

1  | Luke Skywalker  |     1    | 20190211      |     NULL         |
1  | Darth Vader     |     2    | 20190211      |     NULL         |
1  | Tony Stark      |     1    | 20190211      |     NULL         |
1  | Bruce Wayne     |     3    | 20190211      |     NULL         |
1  | Connor MacLeod  |     5    | 20190211      |     NULL         |

联系人组表中的“id”字段与联系人订阅者表中的“group_id”字段匹配。

使用上面的示例，我们正在寻找以下输出:

id | name      | Value

1  | January   | 2
2  | February  | 1
3  | March     | 1
4  | April     | 0
5  | May       | 1

我觉得我们已经很接近了，我们能够提出如下所示的代码，它显示 ID 和月份，但我们还无法让计数函数正常工作。这是我们目前拥有的:

SELECT 
contact_group.id,
contact_group.name,
COALESCE(group_id, 0) AS value FROM
contact_group
LEFT JOIN
contact_subscriber ON contact_group.id = contact_subscriber.group_id;

这提供了以下结果:

+----+---------------+-------+
| id | name          | value |
+----+---------------+-------+
|  1 | January       |     1 |
|  1 | January       |     1 |
|  2 | February      |     2 |
|  3 | March         |     3 |
|  4 | April         |     0 |
|  5 | May           |     5 |
+----+---------------+-------+
6 rows in set (0.00 sec)

任何有关使其正常工作的帮助将不胜感激，我已经绞尽脑汁试图解决这个问题大约一周了。

Answer 1

使用聚合函数count()

SELECT 
    contact_group.id    
    contact_group.name,
    count(contact_subscriber.group_id) AS value FROM
    contact_group 
    LEFT JOIN
    contact_subscriber ON contact_group.id = contact_subscriber.group_id
    group by contact_group.name, contact_group.id