sql(mariaDB)で年度ごとに集計したい

Question

いつもお世話になります。お忙しいところ恐縮です。よろしくお願いします。

SQLバージョン：5.5.60-MariaDB

テーブルが２つあります。
table_eigyou
（営業のたびにデータが増えるのでshainbangou1に同じ番号が何度も入力される。）
int smallint date　　　←データ型

id	shainbangou1	eigyouday
1	3	2015-06-2
2	5	2017-11-25
3	3	2018-12-5
.	.	.
.	.	.

table_shain　（社員の番号、名前、所属）
int smallint varchar varchar　　←データ型

id_s	bangou	shain	shozoku
1	3	Aさん	東京支店
2	5	Eさん	福岡支店
.	.	.	.
.	.	.	.
.	.	.	.

上記2つのテーブルを結合して「年度ごと」の各社員の営業回数(eigyouday)を集計したいと思っています。
望む結果は以下です。

shainbangou1	shain	shozoku	2016年度	2017年度	2018年度
3	Aさん	東京支店	5	1	15
5	Eさん	福岡支店	8	20	35
.	.	.	.	.	.
.	.	.	.	.	.
.	.	.	.	.	.

以下のSQLを実行しましたが望む結果が出ません。ご教示お願いいたします。

SELECT shainbangou1, shain, shozoku, 
   CASE WHEN DATE_FORMAT(eigyouday,'%m') < 4 THEN DATE_FORMAT(eigyouday,'%Y') -1
   ELSE DATE_FORMAT(eigyouday,'%Y')
   END
 COUNT(*) AS kazoeru
 FROM table_eigyou AS t1 
 JOIN table_shain AS t2 
 ON t1.shainbangou1 = t2.bangou 
 WHERE eigyouday
 GROUP BY shainbangou1,shain,shozoku

実行結果
MySQL のメッセージ:
1064 - Something is wrong in your syntax : 'COUNT(*) AS kazoeru

SELECT shainbangou1, shain, shozoku, 
COUNT(*) AS kazoeru
FROM table_eigyou AS t1 
JOIN table_shain AS t2 
ON t1.shainbangou1 = t2.bangou 
WHERE eigyouday BETWEEN '2017-04-01' AND '2018-03-31'
GROUP BY shainbangou1,shain,shozoku

実行結果

shainbangou1	shain	shozoku	kazoeru
3	Aさん	東京支店	5
5	Eさん	福岡支店	8

件数に間違いはありませんが、単年度のみとなり毎年度の集計ができません。

SELECT shainbangou1, shain, shozoku, 
COUNT(*) AS kazoeru
FROM table_eigyou AS t1 
JOIN table_shain AS t2 
ON t1.shainbangou1 = t2.bangou 
WHERE eigyouday(BETWEEN '2017-04-01' AND '2018-03-31')
OR(BETWEEN '2018-04-01' AND '2019-03-31')
GROUP BY shainbangou1,shain,shozoku

実行結果
1064 - Something is wrong in your syntax : 'BETWEEN '2017-04-01' AND '2018-03-31')
OR(BETWEEN '2018-04-01' AND '2019-03-31'' 付近 : 6 行目

SELECT shainbangou1, shain, shozoku, 
 COUNT( CASE WHEN DATE_FORMAT(eigyouday,'%m') < 4 THEN DATE_FORMAT(eigyouday,'%Y') -1
   ELSE DATE_FORMAT(eigyouday,'%Y')
   END ) AS kazoeru
 FROM table_eigyou AS t1 
 JOIN table_shain AS t2 
 ON t1.shainbangou1 = t2.bangou 
 WHERE eigyouday
 GROUP BY shainbangou1,shain,shozoku

実行結果

shainbangou1	shain	shozoku	kazoeru
7	iさん	北海道支店	15
15	sさん	大阪支店	30

件数に間違いはありませんが、全ての年度が合算されてしまいます。

SELECT shainbangou1, shain, shozoku, 
 COUNT(*) AS kazoeru
 FROM table_eigyou AS t1 
 JOIN table_shain AS t2 
 ON t1.shainbangou1 = t2.bangou 
 WHERE
   ( CASE WHEN DATE_FORMAT(eigyouday,'%m') < 4 THEN DATE_FORMAT(eigyouday,'%Y') -1
   ELSE DATE_FORMAT(eigyouday,'%Y')
   END AS nendo )
 GROUP BY shainbangou1,shain,shozoku

実行結果
1064 - Something is wrong in your syntax : 'AS nendo )
GROUP BY shainbangou1,shain,shozoku LIMIT 0, 25' 付近 : 9 行目

ご教示お願いいたします。

Comments

[Orlofsky]

https://teratail.com/questions/167224 のように使っているテーブルの CREATE TABLE と INSERT で提示するとSQLをすぐ実行できるので、適切なコメントが付き易いかと。

[uep3vjz9wp]

リンク先勉強になりました。今回はご迷惑をおかけしました。今後は気をつけます。ありがとうございました。

Answer 1

年度別の集計は以下の様になります。

SELECT shainbangou1, shain, shozoku
     , DATE_FORMAT(date_sub(eigyouday, INTERVAL 3 MONTH),'%Y') as nendo
     , COUNT(*) AS kazoeru
FROM table_eigyou AS t1 left JOIN table_shain AS t2 
     ON t1.shainbangou1 = t2.bangou 
GROUP BY shainbangou1,shain,shozoku, DATE_FORMAT(date_sub(eigyouday, INTERVAL 3 MONTH),'%Y')

年度をカラムとして展開するのはクロス集計で以下の様になります。

SELECT shainbangou1, shain, shozoku
     , sum((DATE_FORMAT(date_sub(eigyouday, INTERVAL 3 MONTH),'%Y')='2017')) as count_2017
     , sum((DATE_FORMAT(date_sub(eigyouday, INTERVAL 3 MONTH),'%Y')='2018')) as count_2018
FROM table_eigyou AS t1 left JOIN table_shain AS t2 
     ON t1.shainbangou1 = t2.bangou 
GROUP BY shainbangou1,shain,shozoku

カラムが固定になり、取得する年度を変更するには動的SQLにする必要があるので、
最初のSQLで取得して加工する方が良いかもしれません。

Comments

[uep3vjz9wp]

実行させていただき、問題ございませんでした。ありがとうございますおかげさまで助かりました。2つのコードを今後のためにもよく勉強させていただきます。

Answer 2

本質的な話をすれば集計単位に「年度」が必要であれば
個別データに年度カラムを埋め込んでおくと効率的で汎用化しやすいです。
とくに決算月は会社によって異なるケースが多く、4月から始まる
いわゆるスクールカレンダーを前提は危険です。

データのもたせ方は2018年度より、第10期など会社創業からの
営業期ごとにする手もあります

Answer 3

1064 - Something is wrong in your syntax : 'COUNT(*) AS kazoeru

COUNT(*) AS kazoeru
の前に , がありません。

Comments

[uep3vjz9wp]

,COUNT(*) AS kazoeru ですね。
ご指摘ありがとうございます。修正して実行したところ望む結果とはちょっと違いましたがエラーは出ませんでした。勉強不足でした。ありがとうございました。