Mysql LEFT JOIN with count returns Unknown column

Question

亲爱的，我有下面的查询来计算每个用户的垃圾邮件数量和的总订单

我做了左边的加入，因为不是所有的订单都有垃圾邮件

select users.firstName,users.lastName,users.Id,users.phoneNumber,count(CASE 
WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as countSpam,
count(`orders`.`id`) as totalOrder
from `orders`,users,providers
LEFT JOIN comments ON `orders`.`id`= `comments`.`commentableId`
where
`orders`.`providerId` = `providers`.id
and
users.id = `providers`.userId
and
`orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
GROUP BY users.id
ORDER BY countSpam DESC;

我从mysql 得到以下错误

子句中的未知列'orders.id'

这里有什么问题？我在旧查询的基础上做了正确的LEFT JOIN，工作良好

Answer 1

我解决了这个问题，我认为这是语法问题，新的查询在下面运行得很好

select users.firstName,users.lastName,users.phoneNumber,count(CASE
WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END) as spamCounter,
ROUND(count(CASE
WHEN comments.`commentType` = "spam" THEN 1 ELSE NULL END)/count(orders.id),2) AS ratio_spam,
count(orders.id) as totalOrder
from users,providers,orders
LEFT JOIN comments ON `orders`.`id`= `comments`.`commentableId`
where
orders.`providerId` = providers.id
and
users.id = providers.userId
and
`orders`.`createdAt` >= (CURDATE() - INTERVAL 7 DAY)
GROUP BY users.id
ORDER BY spamCounter DESC
LIMIT 20;