SELECT list is not in GROUP BY clause and contains nonaggregated column

エラー

GROUP BYエラー

　状態:- 　閲覧数:972 　投稿日:2017-08-17 　更新日:2019-01-19 　
実際に遭遇したエラーメッセージ1
PDO ERROR:SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'データベース名.m.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

実際に遭遇したエラーメッセージ2
( ! ) Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'データベース名.res.body' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in

実際に遭遇したエラーメッセージ3
( ! ) Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'データベース名.res.body' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by in


遭遇例1

　閲覧数:248　投稿日:2017-08-17　更新日:2017-10-29　

エラー対応


修正前
public function Get1ExitpageRankingSession()

{



$query = "

  SELECT `user_id`, `visitor_session_id`,`title`, `request_url`, `http_referer`, `user_agent`, `screen_width`, `screen_height`, `access_datetime`,COUNT(*) AS `count` FROM `access` AS m

  WHERE `visitor_session_id` != '' AND NOT EXISTS (

      SELECT 1

      FROM `access` AS s

      WHERE m.`visitor_session_id` = s.`visitor_session_id`

      AND m.`access_datetime` < s.`access_datetime`

  

  AND m.`access_datetime`>=date(current_timestamp - interval 1 day)

  AND m.`access_datetime` < date(now())

  GROUP BY m.`request_url`

  ORDER BY `count` DESC

";

   return $this->executeFetchAll($query);

}


修正後
public function Get1ExitpageRankingSession()

{



$query = "

  SELECT ANY_VALUE(`user_id`) AS user_id, `visitor_session_id`,`title`, `request_url`, `http_referer`, `user_agent`, `screen_width`, `screen_height`, `access_datetime`,COUNT(*) AS `count` FROM `access` AS m

  WHERE `visitor_session_id` != '' AND NOT EXISTS (

      SELECT 1

      FROM `access` AS s

      WHERE m.`visitor_session_id` = s.`visitor_session_id`

      AND m.`access_datetime` < s.`access_datetime`

  

  AND m.`access_datetime`>=date(current_timestamp - interval 1 day)

  AND m.`access_datetime` < date(now())

  GROUP BY m.`request_url`

  ORDER BY `count` DESC

";

   return $this->executeFetchAll($query);

}


ANY_VALUE指定しても解決しない場合は？


一旦、ORDER BYをコメントアウトしてみる
・もしかすると不要な指定かも
※「ORDER BY」も、一意かどうかの判定に利用されるため注意が必要

遭遇例2

　閲覧数:300　投稿日:2017-10-29　更新日:2020-03-15　

エラー対応


修正前
public function fetchByParentCategoryid2new($category_id)

{	

$sql = "

  SELECT

    a.id AS id,

    a.category_id AS category_id,

    a.state_id AS state_id,

    a.counter AS counter,

    a.created_at AS created_at,

    a.modified AS modified,

    a.body AS body,

    topic_name,

    r.modified AS rmodified,

    r.body AS rbody,

    u.display_name, u.site_id, c.category, c.parent_id,s.situation, s.fonticon,s.color,relationentrynum

  FROM

    status AS a

  LEFT JOIN user u ON a.user_id = u.id

  LEFT JOIN category c ON a.category_id = c.category_id

  LEFT JOIN state s ON a.state_id = s.state_id

	LEFT OUTER JOIN( 

    SELECT

      topic_id,`body`, MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`

  	FROM

      res

    GROUP BY topic_id

  ) AS r ON a.id = r.topic_id";

$where = array();

$bind = array();

foreach ($category_id as $val) {

  $where[] = 'a.`category_id` = ? ';//単一キーワード

  $bind[] = '' . $val . '';

}

$sql .= ' where ' . implode('or ', $where);

$sql .= ' and public = 1 ';

$sql .= 'ORDER BY a.created_at ASC';

return $this->fetchAll($sql, $bind);

}


修正後
ANY_VALUE(`body`)
public function fetchByParentCategoryid2new($category_id)

{	

$sql = "

  SELECT

    a.id AS id,

    a.category_id AS category_id,

    a.state_id AS state_id,

    a.counter AS counter,

    a.created_at AS created_at,

    a.modified AS modified,

    a.body AS body,

    topic_name,

    r.modified AS rmodified,

    r.body AS rbody,

    u.display_name, u.site_id, c.category, c.parent_id,s.situation, s.fonticon,s.color,relationentrynum

  FROM

    status AS a

  LEFT JOIN user u ON a.user_id = u.id

  LEFT JOIN category c ON a.category_id = c.category_id

  LEFT JOIN state s ON a.state_id = s.state_id

	LEFT OUTER JOIN( 

    SELECT

      topic_id,ANY_VALUE(`body`) AS body, MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`

  	FROM

      res

    GROUP BY topic_id

  ) AS r ON a.id = r.topic_id";

$where = array();

$bind = array();

foreach ($category_id as $val) {

  $where[] = 'a.`category_id` = ? ';//単一キーワード

  $bind[] = '' . $val . '';

}

$sql .= ' where ' . implode('or ', $where);

$sql .= ' and public = 1 ';

$sql .= 'ORDER BY a.created_at ASC';

return $this->fetchAll($sql, $bind);

}



・セッション系のSQLでエラーが発生。仕組みだけ作成して、クッキー設定していなかったから？

遭遇例3

　閲覧数:219　投稿日:2019-01-19　更新日:2019-01-19　

エラー修正内容


body を ANY_VALUE(`body`) AS body へ修正

エラー修正前


    public function fetchByStateid2($state_id)

    {

        $sql = "

SELECT        

	a.id AS id,

	a.category_id AS category_id,

	a.state_id AS state_id,

	a.body AS body,

	a.created_at AS created_at,

	a.modified AS modified,

	topic_name,

	r.body AS rbody,

	r.modified AS rmodified,

	 u.display_name, u.site_id, c.category, s.situation, s.color,relationentrynum

       

FROM

	status AS a



	LEFT JOIN user u ON a.user_id = u.id

	LEFT JOIN category c ON a.category_id = c.category_id

	LEFT JOIN state s ON a.state_id = s.state_id



	LEFT OUTER JOIN( 

		SELECT

			topic_id,body, MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`



		FROM

			res

		GROUP BY topic_id

	) AS r ON a.id = r.topic_id

        



   

                WHERE a.state_id =:state_id

                    AND public = 1

ORDER BY created_at DESC

        ";



// echo $sql;





        return $this->fetchAll($sql, array(

            ':state_id'        => $state_id,



        ));

    }      


エラー修正後


    public function fetchByStateid2($state_id)

    {

        $sql = "

SELECT        

	a.id AS id,

	a.category_id AS category_id,

	a.state_id AS state_id,

	a.body AS body,

	a.created_at AS created_at,

	a.modified AS modified,

	topic_name,

	r.body AS rbody,

	r.modified AS rmodified,

	 u.display_name, u.site_id, c.category, s.situation, s.color,relationentrynum

       

FROM

	status AS a



	LEFT JOIN user u ON a.user_id = u.id

	LEFT JOIN category c ON a.category_id = c.category_id

	LEFT JOIN state s ON a.state_id = s.state_id



	LEFT OUTER JOIN( 

		SELECT

			topic_id,ANY_VALUE(`body`) AS body, MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`



		FROM

			res

		GROUP BY topic_id

	) AS r ON a.id = r.topic_id

        



   

                WHERE a.state_id =:state_id

                    AND public = 1

ORDER BY created_at DESC

        ";



// echo $sql;





        return $this->fetchAll($sql, array(

            ':state_id'        => $state_id,



        ));

    }      


遭遇例4

　閲覧数:203　投稿日:2020-03-15　更新日:2020-04-05　

修正前


         public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)

    {

// SELECT DISTINCT et.* FROM

// エントリーテーブル et

// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec

// ON (et.エントリーID = ec.エントリーID)

// WHERE ec.カテゴリーID = 1

      

      

      

        $sql = "

        





        

SELECT

	a.id AS id,

	a.category_id AS category_id,

  a.project_id AS project_id,

  a.domain_id AS domain_id,

	a.state_id AS state_id,

  a.official_url AS official_url,

  a.body AS body,

  a.counter AS counter,

	a.created_at AS created_at,

	a.modified AS modified,

	a.topic_name,

  z.*,

  s.situation,

  s.color AS statecolor,

	r.modified AS rmodified,

	u.display_name, 

	u.site_id,

	c.category,

	p.project,

	d.domain,

	relationentrynum,

	GROUP_CONCAT(t.tag) AS tag,

	GROUP_CONCAT(t.color) AS tag_color,

	GROUP_CONCAT(st.tag_id) AS tag_id,

  c.color AS category_color,

  p.color AS project_color,

  d.color AS domain_color

FROM

	status AS a



	LEFT JOIN user u ON a.user_id = u.id

  LEFT JOIN category c ON a.category_id = c.category_id

  LEFT JOIN project p ON a.project_id = p.project_id

  LEFT JOIN domain d ON a.domain_id = d.domain_id

  LEFT JOIN status_tag st ON (a.id = st.status_id)

  LEFT JOIN state s ON a.state_id = s.state_id

  RIGHT JOIN tag t ON (t.tag_id = st.tag_id)

	LEFT OUTER JOIN( 

		SELECT

			topic_id,MAX(modified) AS modified,	COUNT(topic_id) AS `relationentrynum`

		FROM

			res

		GROUP BY topic_id

	) AS r ON a.id = r.topic_id



  LEFT JOIN( 

    SELECT

      a.category_id, a.category, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.category

        else b.category

        end AS parent_category,

        CASE WHEN a.parent_id = 0 

        then a.category_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      category a

    LEFT JOIN

      category b on a.parent_id = b.category_id        

  ) AS z ON a.category_id = z.category_id



  LEFT JOIN( 

    SELECT

      a.project_id, a.project, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.project

        else b.project

        end AS parent_project,

        CASE WHEN a.parent_id = 0 

        then a.project_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      project a

    LEFT JOIN

      project b on a.parent_id = b.project_id        

  ) AS x ON a.project_id = x.project_id



  LEFT JOIN( 

    SELECT

      a.domain_id, a.domain, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.domain

        else b.domain

        end AS parent_domain,

        CASE WHEN a.parent_id = 0 

        then a.domain_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      domain a

    LEFT JOIN

      domain b on a.parent_id = b.domain_id        

  ) AS y ON a.domain_id = y.domain_id



WHERE a.public = 1

GROUP BY a.id

ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC,  a.`created_at` DESC



";

        return $this->fetchAll($sql);

    }


JOIN追加したらエラー発生


LEFT JOIN `chronology` chr ON (a.id = chr.status_id)
`chr`.`modified` AS chrmodified,
         public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)

    {

// SELECT DISTINCT et.* FROM

// エントリーテーブル et

// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec

// ON (et.エントリーID = ec.エントリーID)

// WHERE ec.カテゴリーID = 1

      

      

      

        $sql = "

        





        

SELECT

	a.id AS id,

	a.category_id AS category_id,

  a.project_id AS project_id,

  a.domain_id AS domain_id,

	a.state_id AS state_id,

  a.official_url AS official_url,

  a.body AS body,

  a.counter AS counter,

	a.created_at AS created_at,

	a.modified AS modified,

	a.topic_name,

  z.*,

  s.situation,

  s.color AS statecolor,

	r.modified AS rmodified,

	u.display_name, 

	u.site_id,

	c.category,

	p.project,

	d.domain,

	`chr`.`modified` AS chrmodified,

	relationentrynum,

	GROUP_CONCAT(t.tag) AS tag,

	GROUP_CONCAT(t.color) AS tag_color,

	GROUP_CONCAT(st.tag_id) AS tag_id,

  c.color AS category_color,

  p.color AS project_color,

  d.color AS domain_color

FROM

	status AS a



	LEFT JOIN user u ON a.user_id = u.id

  LEFT JOIN category c ON a.category_id = c.category_id

  LEFT JOIN project p ON a.project_id = p.project_id

  LEFT JOIN domain d ON a.domain_id = d.domain_id

  LEFT JOIN status_tag st ON (a.id = st.status_id)

  LEFT JOIN state s ON a.state_id = s.state_id

  LEFT JOIN `chronology` chr ON (a.id = chr.status_id)

  RIGHT JOIN tag t ON (t.tag_id = st.tag_id)

	LEFT OUTER JOIN( 

		SELECT

			topic_id,MAX(modified) AS modified,	COUNT(topic_id) AS `relationentrynum`

		FROM

			res

		GROUP BY topic_id

	) AS r ON a.id = r.topic_id



  LEFT JOIN( 

    SELECT

      a.category_id, a.category, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.category

        else b.category

        end AS parent_category,

        CASE WHEN a.parent_id = 0 

        then a.category_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      category a

    LEFT JOIN

      category b on a.parent_id = b.category_id        

  ) AS z ON a.category_id = z.category_id



  LEFT JOIN( 

    SELECT

      a.project_id, a.project, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.project

        else b.project

        end AS parent_project,

        CASE WHEN a.parent_id = 0 

        then a.project_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      project a

    LEFT JOIN

      project b on a.parent_id = b.project_id        

  ) AS x ON a.project_id = x.project_id



  LEFT JOIN( 

    SELECT

      a.domain_id, a.domain, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.domain

        else b.domain

        end AS parent_domain,

        CASE WHEN a.parent_id = 0 

        then a.domain_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      domain a

    LEFT JOIN

      domain b on a.parent_id = b.domain_id        

  ) AS y ON a.domain_id = y.domain_id



WHERE a.public = 1

GROUP BY a.id

ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC,  a.`created_at` DESC



";

        return $this->fetchAll($sql);

    }


修正対応


SQLを一意に特定できるよう、GroupBy句に取得するカラムを指定
GROUP BY に chr.modified を追加
GROUP BY a.id,chr.modified
         public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)

    {

// SELECT DISTINCT et.* FROM

// エントリーテーブル et

// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec

// ON (et.エントリーID = ec.エントリーID)

// WHERE ec.カテゴリーID = 1

      

      

      

        $sql = "

        





        

SELECT

	a.id AS id,

	a.category_id AS category_id,

  a.project_id AS project_id,

  a.domain_id AS domain_id,

	a.state_id AS state_id,

  a.official_url AS official_url,

  a.body AS body,

  a.counter AS counter,

	a.created_at AS created_at,

	a.modified AS modified,

	a.topic_name,

  z.*,

  s.situation,

  s.color AS statecolor,

	r.modified AS rmodified,

	u.display_name, 

	u.site_id,

	c.category,

	p.project,

	d.domain,

	`chr`.`modified` AS chrmodified,

	relationentrynum,

	GROUP_CONCAT(t.tag) AS tag,

	GROUP_CONCAT(t.color) AS tag_color,

	GROUP_CONCAT(st.tag_id) AS tag_id,

  c.color AS category_color,

  p.color AS project_color,

  d.color AS domain_color

FROM

	status AS a



	LEFT JOIN user u ON a.user_id = u.id

  LEFT JOIN category c ON a.category_id = c.category_id

  LEFT JOIN project p ON a.project_id = p.project_id

  LEFT JOIN domain d ON a.domain_id = d.domain_id

  LEFT JOIN status_tag st ON (a.id = st.status_id)

  LEFT JOIN state s ON a.state_id = s.state_id

  LEFT JOIN `chronology` chr ON (a.id = chr.status_id)

  RIGHT JOIN tag t ON (t.tag_id = st.tag_id)

	LEFT OUTER JOIN( 

		SELECT

			topic_id,MAX(modified) AS modified,	COUNT(topic_id) AS `relationentrynum`

		FROM

			res

		GROUP BY topic_id

	) AS r ON a.id = r.topic_id



  LEFT JOIN( 

    SELECT

      a.category_id, a.category, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.category

        else b.category

        end AS parent_category,

        CASE WHEN a.parent_id = 0 

        then a.category_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      category a

    LEFT JOIN

      category b on a.parent_id = b.category_id        

  ) AS z ON a.category_id = z.category_id



  LEFT JOIN( 

    SELECT

      a.project_id, a.project, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.project

        else b.project

        end AS parent_project,

        CASE WHEN a.parent_id = 0 

        then a.project_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      project a

    LEFT JOIN

      project b on a.parent_id = b.project_id        

  ) AS x ON a.project_id = x.project_id



  LEFT JOIN( 

    SELECT

      a.domain_id, a.domain, a.parent_id, 

        CASE WHEN a.parent_id = 0 

        then a.fonticon

        else b.fonticon

        end AS fonticon,

        CASE WHEN a.parent_id = 0 

        then a.color

        else b.color

        end  AS color,

        CASE WHEN a.parent_id = 0 

        then a.domain

        else b.domain

        end AS parent_domain,

        CASE WHEN a.parent_id = 0 

        then a.domain_id

        else a.parent_id

        end AS parent_id2      

    FROM 

      domain a

    LEFT JOIN

      domain b on a.parent_id = b.domain_id        

  ) AS y ON a.domain_id = y.domain_id



WHERE a.public = 1

GROUP BY a.id,chr.modified

ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC,  a.`created_at` DESC



";

        return $this->fetchAll($sql);

    }



→ MySQLでSELECT文を作成するための基本的な考え方。どの構文を採用するか、までの思考履歴

#3106 - 'Fulltext index on virtual generated column' is not supported for generated columns.

クエリエラー：　#1294 - 列 'カラム名' に ON UPDATE旬は無効です。

コメント投稿(ログインが必要)



類似度ページランキング
順位 ページタイトル抜粋 ％
1 SELECT list is not in GROUP BY clause and contains nonaggregated column 38
2 supplied argument is not a valid MySQL result 28
3 Incorrect integer value 26
4 Data truncated for column 25
5 where clause is ambiguous 25
6 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 24
7 ( ! ) Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value 23
8 #1067 - Invalid default value for 'id' 20
9 #3106 - 'Fulltext index on virtual generated column' is not supported for generated columns. 18
10 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 16
11 エラー: 1118 SQLSTATE: 42000 (ER_TOO_BIG_ROWSIZE) 15
12 grant 13
13 MySQLで、カラム内の指定文字を削除するためには、replace();を使用します。 12
14 クエリエラー：　#1294 - 列 'カラム名' に ON UPDATE旬は無効です。 11
15 #1241 - オペランドに 1 個の列が必要です。 2
16 クエリエラー: #1265 - 列 'カラム名' の 行 2
17 クエリーキャッシュ 0
18 <> 0
19 テーブルロック 0
20 スキーマ 0
2025/4/25 17:43 更新
週間人気ページランキング ／ 4-18 → 4-24
順位 ページタイトル抜粋 アクセス数
1 #1241 - オペランドに 1 個の列が必要です。 | エラー 10
2 クエリエラー: #1265 - 列 'カラム名' の 行 ★ でデータが切り捨てられました。 | エラー 5
2 where clause is ambiguous | エラー 5
3 ( ! ) Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value | エラー 4
4 クエリエラー：　#1294 - 列 'カラム名' に ON UPDATE旬は無効です。 | エラー 3
5 SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry | エラー 2
5 正規化 2
5 を未入力のまま送信した場合の問題 2
5 #1067 - Invalid default value for 'id' | エラー 2
5 スキーマ | データベースの構造 2
5 エラー: 1118 SQLSTATE: 42000 (ER_TOO_BIG_ROWSIZE) | エラー 2
5 クエリーキャッシュ | チューニング 2
5 Data truncated for column | エラー 2
5 ログイン 2
6 MySQLで、カラム内の指定文字を削除するためには、replace();を使用します。 | SQL 1
6 遭遇例1.INT型カラムに空文字INSERT 1
6 Incorrect integer value | エラー 1
6 インデックス | 最適化 1
6 SELECT list is not in GROUP BY clause and contains nonaggregated column | エラー 1
6 <> | 演算子 1
2025/4/25 1:01 更新