输出两个关联表的总计的查询没有正确返回总计,为什么?

connected_accounts (fields: id, user_id, account_name)
contacts (fields: id, user_id, connected_account_id, type)
user_recommendations (fields: id, user_id, contact_id)

我试图输出一个查询,显示数据库中每个connect#account记录的关联联系人和用户建议的总数。到目前为止,我掌握的情况如下:

SELECT 
    ca.id, 
    ca.user_id, 
    ca.account_name,
    COUNT(c.id) AS Total
FROM connected_accounts ca
LEFT JOIN contacts c
     ON ca.id = c.connected_account_id
       AND c.contact_type = 'email'
WHERE ca.resource_type = 'calendar.readonly'
ORDER BY ca.id DESC
LIMIT 8

这工作不正常。。。所需的输出如下所示:

结果

id | user_id | account_name | Total Contacts | Total User Recommendations

我还没有加入用户推荐,因为我的联系人加入效果不理想。

我在查询中做错了什么?谢谢您

更新时间:

SELECT 
    ca.id, 
    ca.user_id, 
    ca.account_name,
    COUNT(c.id) AS 'Total Contacts',
    COUNT(pr.id) AS 'Total User Rec.'
FROM connected_accounts ca

LEFT JOIN contacts c
     ON ca.id = c.connected_account_id
       AND c.contact_type = 'email'

LEFT JOIN user_recommendations pr
     ON ca.id = pr.connected_account_id

WHERE ca.resource_type = 'calendar.readonly'
GROUP BY ca.id, ca.user_id, ca.account_name
ORDER BY ca.id DESC
LIMIT 8

以上更新。。上面更新的查询返回的是相同的#for Total Contacts & Total User Rec --这两个号码都是相同的,是总联系人的正确号码。。这个 总用户记录 不会被退回。。。为什么?