mysql - 起诉 Laravel 查询生成器时，每月计算客户数量不起作用

Question

我的 SQL 查询工作正常。使用 SQL 计算每月客户数量

SELECT YEAR(created_at) AS Year, MONTH(created_at) AS Month, COUNT(customer_id) as Customers FROM customers GROUP BY Year, Month

与此等效的查询生成器。

$customers_permonth = DB::table('customers')->select('YEAR(created_at) as Year','MONTH(created_at) as Month')->groupBy('Year','Month')->count();

错误是:SQLSTATE[42S22]:未找到列:1054“group statements”中的未知列“Year”(SQL:选择 count(*) 作为来自 customers group by Year 的聚合， 月)"

Answer 1

此查询将为您获取所有每月客户

$customers_permonth = DB::table('customers')::selectRaw('COUNT(*) as count, YEAR(created_at) year, MONTH(created_at) month')
->groupBy('year', 'month')
->get();