#!/bin/sh
db=${DB:-imoni.sq3}
nlt="
	"		# 改行文字+Tab
sqlite3 $db "SELECT date FROM datelist;" \
  | sort -n -t / \
  | { while read d; do
	# パイプの先の while 内で設定した変数を使いたいので
	# { } でグルーピング
        sq1=$sq1${sq1:+,$nlt}"CASE date WHEN '$d' THEN okng END \"$d\""
        sq2=$sq2${sq2:+,$nlt}"max(\"$d\") \"$d\""
        sq3=$sq3${sq3:+,$nlt}"count(\"$d\")"
        sq4=$sq4${sq4:+, }"\"$d\""
      done
      cat<<EOF
WITH sched2 AS (
  SELECT name,
     $sq1
  FROM sched
) SELECT name,
     $sq2
  FROM sched2 GROUP BY name

  UNION ALL

  SELECT '○の合計',
     $sq3
  FROM sched2
  WHERE '○' IN ($sq4);
EOF
    }