複数条件で集計を行いたい

Question

SQL初心者です。
Access2013を使用して開発を行っております。
ファイルの拡張子は「.mdb(2002 -2003 ファイル形式)」です。

以下のようなテーブルがあります。
一時間ごとに回数を集計しております。

【test_table】
[ID] [日付] [時間] [カテゴリ] [個数]
01 20150909 00 1 10
01 20150909 00 2 5
01 20150909 01 1 10
01 20150909 02 1 10
……
01 20150909 21 1 10
01 20150909 22 1 5
01 20150909 22 2 10
01 20150909 23 1 10

このテーブルのデータに対して
ID、日付、カテゴリごとに、ある時間範囲で個数の集計を行いたいと思っております。

[ID] [日付] [カテゴリ] [昼間集計(09-18)] [夜間集計(19-08)]
01 20150909 1 120 250
01 20150909 2 100 25

以下のようなクエリを作成してみました。

SELECT ID, 日付, 
 (SELECT SUM(個数) WHERE ID='01' AND 日付='20150909' 
                    AND ('09'<= 時間 AND 時間 <='18')AS 昼間集計(09-18),
(SELECT SUM(個数) WHERE ID='01' AND 日付='20150909' 
                    AND (('00'<= 時間 AND 時間 <='08') OR ('19'<= 時間 AND 時間 <='23') )AS 夜間集計,
FROM test_table
GROUP BY ID, 日付, カテゴリ

求める結果は抽出されましたが、SQL自体が長くなってしまい、
もっと短くできないかと思い質問させていただきました。

Answer 1

AcccessだとTRANSFORM(クロス集計クエリ)を使うとシンプルになります。

TRANSFORM Sum(個数)
SELECT ID, 日付, カテゴリ
FROM test_table
WHERE ID="01" AND 日付="20150909"
GROUP BY ID, 日付, カテゴリ
PIVOT IIf(時間 Between "09" And "18","昼間集計(09-18)","夜間集計(19-08)");

ちなみに、前の回答者さんのコードの IFF は IIF のタイプミスですね。

Comments

[f-hanako]

返信が遅くなってしまい申し訳ありません。
回答ありがとうございました。

TRANSFORM句は初めて見ました！便利な機能ですね。

実際にSQLを実行し、求める結果が出力されました。
しかし、後から個数以外にも集計する項目が増えてしまったため、
他でクロス集計が必要なときに使用してみます。
（調べてみると、一つの項目に対してしか使用できないようなので……。）

ありがとうございました！

[hirohiro]

IIFのご指摘ありがとうございます。投稿修正しました。おはずかしい

Answer 2

初めまして。
access vba を使用できるのなら、何度も出てくるものを変数として持たせてしまうのはいかがでしょうか？
その方が読みやすくなりますよ。
また、蛇足ではありますが、業務で利用していた時にANDやWHERE といったキーワードで改行をしていました。
WHERE a = 0 AND b = 0
↓
WHERE a = 0
AND b = 0
という具合です。
参考になれば幸いです。

Comments

[f-hanako]

返信が遅くなってしまい申し訳ありません。
回答ありがとうございました。

繰り返し出てくる時間の範囲を変数として持つことにしました。
おかげで短く記述することが出来ました。
改行箇所も参考にしたいと思います。

ありがとうございました！

Answer 3

SQLは縦に並んでいたものを横にするのが苦手なのであまりスマートにはいきませんが、次のようなコードではどうでしょうか？

SELECT ID,日付,カテゴリ
  ,SUM( IIF(('09'<= 時間 AND 時間 <='18'),個数,0) ) AS 昼間集計(09-18)
  ,SUM( IIF(('09'<= 時間 AND 時間 <='18'),0,個数) ) AS 夜間集計
FROM test_table
WHERE ID='01' AND 日付='20150909'
GROUP BY ID, 日付, カテゴリ

※2015/09/16　IFF->IIFに修正
Accessが手元に無いので動作確認はしていません。

Comments

[f-hanako]

返信が遅くなってしまい申し訳ありません。
回答ありがとうございました。

SQLの中にIIF関数を組み込むことができるのですね。大変助かりました。
おかげで初めより短いSQL文を完成させることが出来ました。
今回のベストアンサーとさせていただきます。

ありがとうございました！

[hirohiro]

うぉ、IFF->IIFの間違いですね。
修正しておきました。すみません