色々と前提があるんですが、今回を機にそこはきちんと整備してやってもらいたいがためにかなり長くなっています。
前提1
DBをプログラムからではなく直接見れる環境を整えよう。
「質問に提示してください」とお願いした「CREATE TABLE文」のことですが、
そのまま「テーブルを作る際に実行されているSQL文」のことです。
質問に追記いただいた情報から、おそらくRubyから実行されているのではないかなと。
データベースはプログラムからすると外部の情報として扱うことになります。
ということは何か問題が起きた時に、プログラムを確認するのではなく、実際のデータベースを見に行ってデータや設定などを確認することになります。
また、SQLと呼ばれるコードを実行して問い合わせを行いますが、それはプログラムから実行する場合も直に実行する場合も同じです。
プログラムから実行しようと思うと、画面遷移があったり入力があったり手間が発生します。
また、別途コメントで書いた通り、「DBに対して直接実行して失敗するSQLはプログラムから実行しても失敗する」ので、
直接DBに対して実行して、正しいSQLを生成してから、そのSQLの形になるようにプログラムを組むのが本来あるべき姿です。
だって、プログラムからDBを利用しようと思ったら、まずDB作ってテーブル作って、テストデータも入れることもありますよね?
プログラムを組む前にDBの準備ができているので、「こういうSQLを実行すればプログラム側でほしい情報をとってこれる(入れられる、更新できる、削除できる)」ものを作っておけば
それがある種のゴールになるのでプログラムもスムーズに作ることができます。
PostgreSQLであればPgAdminが有名どころですかね。
現場では複数の種類のDBの確認ができる[A5:SQL Mk-2]というのもよく採用されます。
いずれにしてもDBの情報を視認しやすいツールは必須です(もちろんコマンドから直接ログインもできますがGUIのほうが見やすい人は多いです)
そういったツールがあればプログラムから作ったテーブル情報でもCREATE TABLE文は出力しやすいです。
CREATE TABLE文を提供することができれば、今回でいえばRubyを実行できる環境がなくても情報の再現確認ができます。
できればサンプルデータのINSERT文があればなおよしです。回答のためにデータを考えたり、質問内容からくみ取るのって結構大変ですからね。
こういったツールは導入、活用してください。
前提2
エラーを読みましょう。
上記の記事にもありますが、英語が得意でなくてもGoogle翻訳にかけるだけでも意味が分かる言葉になります。
column "books.id" must appear in the GROUP BY clause or be used in an aggregate function
Google翻訳そのまま:列 "books.id"はGROUP BY句に含まれているか、集計関数で使用されている必要があります。
本題
確かに日本語としては読める文章になりましたが、
プログラムとしてはある程度理解していないと「どういうこと?」となりますね。
キーワードは幾つかあります。
「GROUP BY」または「集約関数」というものです。
そこで次はマニュアルを読みます。
※バージョンは古いですが、考え方は同じです。
集約関数とはざっくり言うと「特定の列に対して特定の条件で集約する処理をする関数」です。
総和(すべてを足したもの)を求めたり、平均を求めたり。
つまり、この集約関数を実行することで実行結果がまとまります。
これを「得点」で「sum()」を使うと60が算出されます。
select sum(得点) as sum_point from user
ここで「select句」にsum(得点) as sum_pointとだけ書きました。他の列は選んでいません。
※しれっと「 as xxxx」というのを使っていますが、これは「エイリアス」で、要は「別名」ですね。
テーブル名が長かった場合に省略した名前だったり、今回のように集約関数を使うとそのままsum(得点)というカラム名になるのでわかりやすくなるように別名をつけて扱いやすくしています。
それはなぜか?
「集約関数」であるがゆえに、列情報を集約します。
複数の入力行から 1 つの結果を計算するという仕様から、「1つの結果を集約した」ものであるわけです。
集約するので、集約の指定がないカラムは通常エラーになります(MySQLではエラーになりませんでしたが、SQLiteもならない感じでしょうね)
次は今回の問題であるGROUP BYです。
仕様は下記
GROUP BY 句句は、テーブル内で選択された全列で同じ値を共有する行をまとめてグループ化するために使用されます。
「グループ化する」ということは「集約する」と同義になります。
実用的なケースはさておき、簡易例では下記。
ID | 名前 | 出身地 |
---|
1 | aさん | 東京 |
2 | bさん | 大阪 |
3 | cさん | 大阪 |
4 | dさん | 東京 |
5 | eさん | 名古屋 |
ここで「GROUP BY」を使う動機としては「出身地に何があるか知りたい」とかですね。
select 出身地 from user GROUP BY 出身地
すると下記のようになります。
それに加えて「それぞれの出身地が何人いるかも集計したい」となると今度は先にあげた「集約関数」を併用することで可能になります。
select 出身地,count(出身地) as count from user GROUP BY 出身地
すると下記のようになります。
ここで「集約関数」の説明部分をきちんと読んでいれば気づくかもしれません。
「とってきたい列以外指定していない・・?」
そうです。そこが今回の問題を解決する糸口になります。
GROUP BYを指定している場合、select句で指定するカラムは、ほかのカラムもGROUP BYで指定するか、集約関数で取得する必要があります。
ということで今回の問題のSQLを見てみましょう。
SELECT "books".* FROM "books" GROUP BY "books"."year"
books.* としています。booksはテーブル名として、
*
というのは専門用語だと「全列ワイルド・カード」です。
つまり、「全ての列を選択する」という意味になります。
質問本文に「books.idなどというcolumn自体使っていない」とありますし、
提示された「create_table 」から始まるコードには確かに「id」というカラムはないように見えます。
ここからはRuby on Railsの仕様になると思うのですが・・・
create_tableのページを見ますと、
[主キー自動生成がデフォルトtrueでその主キーのカラム名はid」と読み解けます。
という観点で見ると
create_table "books", force: :cascade do |t|
t.text "name"
t.text "name_en"
t.string "year"
t.text "genre"
上記、「主キーの自動生成、および主キーのカラム名」に対する指定がありませんよね。
ということは「主キーがidというカラムで自動生成されている」ということになると思います。
では「前提1」であげました、PgAdminなどのツールでDBの中を覗いてみてください。
おそらく「id」というカラムがあるはずですね。
ということで、
books.* という指定をすると「すべて」列が選択されるので、GROUP BYの仕様通り最初のエラー
列 "books.id"はGROUP BY句に含まれているか、集計関数で使用されている必要があります。
が成り立つわけですね。
集約されているカラムじゃないカラムもすべて選択されているわけですから。
なぜidしかエラーがでないかというと、プログラムは原則として、一番左上端から動いていくので、最初のカラムであるidの時点で、継続不可能なエラーが出て、そこで落ちた(中断した)と考えられます。
ではどうするか
ここからは「じゃあそもそもどういうデータを取得したいか」という
質問者さん自身の要件になってきます。仕様なので質問者さんが決める必要があります。
yearをグループ化したということは、例えば年毎に集約したデータを取りたいのでしょうし、でも年だけなの?というところは質問者さんにしかわかりません。
年だけでいいなら、
下記のようになるようにプログラムに組んでいくことになります。
SELECT "books"."year" FROM "books" GROUP BY "books"."year"
別途コメントしたように、Rubyに明るいわけではないですし、今提示されている情報だけでは「どう組めば良いか」まではわかりません。
「取得したデータをどう使いたいか」というところも「要件・仕様」になるので
その要件や仕様にあわせたデータの取り方にしてください。
※今回は「なぜ今回の問題が起きたか」というところが質問内容なので、だいぶ蛇足を含めて解説しました。tその「なぜ起きたか」がわかれば本件はいったん解決と考えています。
※ちなみに今回私が回答の際に作ったサンプルのCREATE TABLEとINSERTです。
sql
1
2CREATE TABLE "user"
3(
4 id integer NOT NULL,
5 name character varying(10) COLLATE pg_catalog."default" NOT NULL,
6 pref character varying(10) COLLATE pg_catalog."default" NOT NULL
7);
8
9INSERT INTO "user" ("id", "name", "pref") VALUES
10(1, 'a', '東京'),
11(2, 'b', '大阪'),
12(3, 'c', '東京'),
13(4, 'd', '大阪'),
14(5, 'e', '名古屋'),
15(6, 'f', '大阪'),
16(7, 'g', '名古屋'),
17(8, 'h', '福岡');
※ちなみにSQLはDBの種類によって方言があり、このCREATE TABLEも別の環境やDBでそのまま通るとは限りません。私の環境で抽出したものですし(PostgreSQL11 PgAdmin4)
既に回答がついているように開発環境と本番環境含めて全て同じ種類、同じバージョンでされた方がトラブルなく済みます。これはプログラムについても同じですね。
マイナーバージョンでも1つ違えば使えなくなる機能、非推奨の機能があってそれだけで同じ挙動は期待できません。