phpmyadmin Question posted in
The official documentation can be found here.

How to get the total amount in a database table field month wise of the current year using laravel – Phpmyadmin

So I have database with amount and created_at field on it and I want to the total amount per month of the current year and or every year.

$sql = 'SELECT SUM(amount) as "Amount",MONTHNAME(created_at) as "Month"
FROM transactions WHERE YEAR(created_at) = YEAR(CURDATE())
GROUP BY YEAR(created_at),MONTH(created_at)';
DB::statement($sql);

Actual Table

ID    amount     created_at
1      1000      2019-10-24    
2      234       2019-11-11
3      122       2019-09-04
4      5200      2019-09-14

Expected Result 

ID    Amount     Month
1      6322      September    
2      234       November

Syntax error or access violation: 1055 db.table.created_at isn’t in Group by(SQL: SELECT SUM(amount) as “Total Amount”, MONTHNAME(created_at) as ” Month

Please what is it saying. However if run this query on phpmyadmin it works perfectly well

Tags:

2

Answers


  1. 0

    First set the strict => false in appconfigdatabase -> mysql

    after that run php artisan optimize

    then 

     $sql = DB::table('transactions')
        ->select(DB::raw('SUM(amount) as Amount,MONTHNAME(created_at) as Month Name'))
        ->where(DB::raw('YEAR(created_at) = YEAR(CURDATE())'))
        ->groupBy('created_at')
        ->get();
    Login or Signup to reply.
  2. 0

    You’re grouping on portions of created_at, then referring to created_at itself when you SELECT. The issue is that only aggregate functions and the things grouped on can be used when you group results in the query. If you use MONTH(created_at) in select instead of MONTHNAME you should be fine (or you could group by YEAR, MONTHNAME instead and continue to use MONTHNAME in your projection

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search