Search code examples
mysqllaravellaravel-query-builder

Count customers monthly is not working suing Laravel query builder

My SQL Query is working fine. count customers per month in SQL 

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

Query Builder equivalent to this.

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

Error is : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Year' in 'group statement' (SQL: select count(*) as aggregate from customers group by Year, Month)"

Solution

  • This query will fetch you all monthly customers

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