Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

arctushar's avatar

Error in query but same is working in phpmyadmin query

My laravel code is as below

$portfolio = Portfolio::selectRaw('portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnlotal')
			->leftJoin('pnls','pnls.portfolio_id','portfolios.id')
			->groupBy('portfolios.id')
			->get();

The above code giving error as

SQLSTATE[42000]: Syntax error or access violation: 1055 'maindb.portfolios.quantity' isn't in GROUP BY (SQL: select portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnlotal from `portfolios` left join `pnls` on `pnls`.`portfolio_id` = `portfolios`.`id` group by `portfolios`.`id`)

To get sql output i used ->toSQL and this output is as below

select portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnlotal from `portfolios` left join `pnls` on `pnls`.`portfolio_id` = `portfolios`.`id` group by `portfolios`.`id`
``

When I put the above result to phpmyadmin sql query, it is showing accurate result. 

Why Laravel is getting error? What is the solution
0 likes
8 replies
jlrdw's avatar

You can use the DB facade and write the query that works.

arctushar's avatar

I have tried by

DB::table('portfolios')
			->selectRaw('portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnltotal')
			->leftJoin('pnls','portfolios.id','pnls.portfolio_id')
			->groupBy('portfolios.id')
			->get()

This also showing error as

SQLSTATE[42000]: Syntax error or access violation: 1055 'raigattu_maindb.portfolios.quantity' isn't in GROUP BY (SQL: select portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnltotal from `portfolios` left join `pnls` on `portfolios`.`id` = `pnls`.`portfolio_id` group by `portfolios`.`id`)
1 like
trin's avatar

your mysql starts with flag ONLY_FULL_GROUP_BY, u need to use aggregate function on ungrouping field, or add it to GROUP BY

  • use ANY_VALUE to portfolios.quantity like portfolios.id, ANY_VALUE(portfolios.quantity). or any other aggregation function like MIN, MAX, SUM, COUNT, etc
  • add portfolios.quantity to GROUP BY like GROUP BY portfolios.id, portfolios.quantity
1 like
trin's avatar

why it works in phpmyadmin? i dont know, may be u run this query on different server (like on dev laravel, on prod pma)

arctushar's avatar

Thank you so much. This works perfectly. But have little update

return DB::table('portfolios')
			->selectRaw('portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id,pnls.quantity) as pnltotal')
			->leftJoin('pnls','portfolios.id','pnls.portfolio_id')
			->groupBy('portfolios.id','portfolios.quantity','pnls.quantity','pnls.portfolio_id')
			->get();

this works perfect. But when I update to

return DB::table('portfolios')
			->selectRaw('portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id,pnls.quantity) as pnltotal')
			->leftJoin('pnls','portfolios.id','pnls.portfolio_id')
			->groupBy('portfolios.id','portfolios.quantity','pnls.quantity','pnls.portfolio_id')
			->havingRaw('portfolios.quantity > sum(pnls.quantity)')
			->get();

it shows below error.

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'quantity' is used in HAVING clause (SQL: select portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id,pnls.quantity) as pnltotal from `portfolios` left join `pnls` on `portfolios`.`id` = `pnls`.`portfolio_id` group by `portfolios`.`id`, `portfolios`.`quantity`, `pnls`.`quantity`, `pnls`.`portfolio_id` having portfolios.quantity > sum(pnls.quantity))

why after adding havingRaw, it is showing error ? plz Help

trin's avatar

for beginners turn off full group by it is ok, but in general it is bad practice

arctushar's avatar
arctushar
OP
5 years ago
Best Answer
Level 2

solved by

DB::statement("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
				$portfolios=DB::select('select codelists.code,portfolios.action,portfolios.commission,portfolios.price,portfolios.date,portfolios.id,portfolios.quantity,(SELECT SUM(pnls.quantity) GROUP BY pnls.portfolio_id) as pnltotal from `portfolios` left join `pnls` on `portfolios`.`id` = `pnls`.`portfolio_id` left join `codelists` on `codelists`.`id`=`portfolios`.`codelist_id` group by `portfolios`.`id` having portfolios.quantity > sum(pnls.quantity)');
				DB::statement("SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY'));");

Please or to participate in this conversation.