| ページ一覧 | ブログ | twitter |  書式 | 書式(表) |

MyMemoWiki

DB2 GROUP BY 拡張

提供: MyMemoWiki
ナビゲーションに移動 検索に移動

DB2 GROUP BY 拡張

DB2 |

ROLLUP

  • グループごとのサマリーを作成
SELECT t.name AS tour_name,
       c.name AS county_name,
       COUNT(*) AS falls_count
FROM upfall u INNER JOIN trip t
      ON u.id = t.stop
   INNER JOIN county c
      ON u.county_id = c.id
GROUP BY ROLLUP(t.name, c.name);
TOUR_NAME  COUNTY_NAME FALLS_COUNT
---------- ----------- ----------------------
M-28       Alger       3
M-28       Baraga      1
M-28       Ontonogan   2
M-28                   6
US-2       Delta       1
US-2       Gogebic     1
US-2       Dickenson   1
US-2       Ontonogan   1
US-2                   4
Munising   Alger       6
Munising               6

CUBE

  • 指定列のすべての可能な組み合わせサマリ
SELECT t.name AS tour_name,
       c.name county_name,
       COUNT(*) AS falls_count
FROM upfall u INNER JOIN trip t
      ON u.id = t.stop
   INNER JOIN county c
      ON u.county_id = c.id
WHERE t.name = 'Munising'
GROUP BY CUBE(t.name, c.name);
TOUR_NAME  COUNTY_NAME FALLS_COUNT
---------- ----------- ----------------------
                       6
           Alger       6
Munising               6
Munising   Alger       6

GROUP BY で有用な関数

GROUPING( column )

  • CUBE, ROLLUPの結果が NULL の場合、1を返す、そうでない場合、0を返す