How to use SQL clause GROUP BY? I am using Drupal 8 and want to use custom query to get details like below:

$db = \Drupal::database();
$query = $db-&gt;select('sinica_grade_summary','sgs');
$query-&gt;fields('sgs', ['id', 'coursename', 'average', 'grade', 'term', 'schoolname']);
$query-&gt;condition('sgs.uid', $id, '=');
$query-&gt;groupBy("sgs.coursename");


Getting this error :

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 'lmhd8.sgs.id' isn't in GROUP BY: SELECT sgs.id AS id, sgs.coursename AS coursename, sgs.average AS average, sgs.grade AS grade, sgs.term AS term, sgs.schoolname AS schoolname, COUNT(sgs.id) AS count FROM {sinica_grade_summary} sgs WHERE (sgs.uid = :db_condition_placeholder_0) GROUP BY sgs.coursename ORDER BY id DESC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_0] =&gt; 28 ) in Drupal\resume\Controller\ResumeController-&gt;sinica_academic_detail() 


I used mariadb10, there is no sql_mode been used 

mysql  Ver 15.1 Distrib 10.1.13-MariaDB, for osx10.11 (x86_64) using readline 5.1

MariaDB [(none)]&gt; SELECT REPLACE(@@SQL_MODE, ',', '\n');
+--------------------------------------------+
| REPLACE(@@SQL_MODE, ',', '\n')             |
+--------------------------------------------+
| NO_AUTO_CREATE_USER
  NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+


Here is table structure

    CREATE TABLE `sinica_grade_summary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL,
  `studentid` int(10) DEFAULT NULL,
  `courseid` int(10) DEFAULT NULL,
  `leadcourseid` int(11) DEFAULT NULL COMMENT '对应 courseid 关系',
  `coursename` varchar(64) DEFAULT NULL,
  `teacher` varchar(64) DEFAULT NULL,
  `teacher_email` varchar(32) DEFAULT NULL,
  `average` varchar(64) DEFAULT NULL,
  `grade` varchar(64) DEFAULT NULL,
  `status` varchar(64) DEFAULT NULL,
  `createtime` int(11) DEFAULT NULL,
  `markingperiodid` int(11) DEFAULT NULL,
  `durationid` int(11) DEFAULT NULL,
  `term` varchar(64) DEFAULT NULL,
  `gradelevel` varchar(32) DEFAULT NULL,
  `schoolname` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sinica_uid` (`uid`),
  KEY `sinica_term` (`term`) USING BTREE,
  KEY `sinica_subject` (`coursename`)
) ENGINE=InnoDB AUTO_INCREMENT=623 DEFAULT CHARSET=utf8mb4 COMMENT='The data table for user entities.';


How to fix this problem?
You can do this to make it work:

$db = \Drupal::database();
$query = $db-&gt;select('sinica_grade_summary','sgs');
$query-&gt;fields('sgs', ['coursename']);
$query-&gt;condition('sgs.uid', $id, '=');
$query-&gt;groupBy("sgs.coursename");