0

I get an error as given below

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'np_bo.fdm.field_deposit_amount_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by:

for the code as given below:

 $sql =  "SELECT n.nid,  fg.field_guest_target_id as guest, fdm.field_deposit_amount_value as deposit, 
              if(sum(fau.field_amount_used_value) IS NULL, 0, sum(fau.field_amount_used_value)) as used ,
              if((fdm.field_deposit_amount_value - sum(fau.field_amount_used_value)) IS NULL, fdm.field_deposit_amount_value ,(fdm.field_deposit_amount_value - sum(fau.field_amount_used_value))) as leftamount
           FROM node n 
             INNER JOIN field_data_field_guest fg ON n.nid = fg.entity_id 
             INNER JOIN field_data_field_deposit_status fds ON n.nid = fds.entity_id 
             INNER JOIN field_data_field_deposit_amount fdm ON n.nid = fdm.entity_id 
             LEFT JOIN field_data_field_deposit_utilized fdu ON fdu.entity_id = n.nid 
             LEFT JOIN field_data_field_amount_used fau ON fau.entity_id = fdu.field_deposit_utilized_value 
             LEFT JOIN field_data_field_bill_status fbs ON fbs.entity_id = fdu.field_deposit_utilized_value
          WHERE type = 'deposit_sale' and 
                fg.field_guest_target_id = :guest and 
                fds.field_deposit_status_value = 'open' and
                (fbs.field_bill_status_value <> 'Nullified' OR fbs.field_bill_status_value IS NULL)      
          GROUP BY n.nid
          ORDER BY n.nid limit 1";
  $options = array(':guest' => $guest);
  $deposit_sales = db_query($sql, $options)->fetchAssoc();
  return $deposit_sales;

what should be the error? and steps I need to adopt to fix it?

harshal
  • 10,252
  • 3
  • 18
  • 23
  • The error is very clear. Also, this error is the subject of numerous questions on SO. Having done your research, what don't you understand about the error? – HoneyBadger Sep 27 '18 at 11:58
  • Hi @HoneyBadger, I saw this https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql?rq=1 and I see the diffierence in the example given as following : https://www.diffchecker.com/Spw3hWbN – harshal Sep 27 '18 at 11:59
  • but stil it does not provide me an solution to my question, I would be grateful to you @hon – harshal Sep 27 '18 at 12:00
  • mention all non aggregate columns in group by – user2042214 Sep 27 '18 at 12:00
  • Hi , I am not able to get your question, can you support me with an answer instead – harshal Sep 27 '18 at 12:03
  • You typically GROUP BY the columns you SELECT, except those who are arguments to set functions. – jarlh Sep 27 '18 at 12:07
  • Hi @jarlh, I am using nid here – harshal Sep 27 '18 at 12:09
  • Yes, but you also select fg.field_guest_target_id , fdm.field_deposit_amount_value etc. Add them to the GROUP BY. – jarlh Sep 27 '18 at 12:10
  • or aggregate them via max(), min(), sum(), etc. – Shadow Sep 27 '18 at 12:24
  • Hi @Shadow, when you say "aggregate them" what is them which you are referring , is it n.nid or fg.field_guest_target_id as guest or fdm.field_deposit_amount_value as deposit ? – harshal Sep 27 '18 at 13:14
  • That's something that only **you** can answer @harshal. We have no clue how your data looks like and what you want to return. – Shadow Sep 27 '18 at 14:08

0 Answers0