例: 各従業員の明細行および各部門の小計行の指定
次の文では、各従業員の明細行と部門の小計行を示した部門別の給与報告書を作成します。
SELECT name, dept_no, salary FROM employee WITH SUM(salary) BY dept_no;
返される結果は次のとおりです。
name dept_no salary ---- ------- ---------- Peterson J 100 25,000.00 Moffit H 100 35,000.00 Jones M 100 50,000.00 Chin M 100 38,000.00 Greene W 100 32,500.00 ---------- Sum(Salary) 180,500.00 Leidner P 300 34,000.00 Phan A 300 55,000.00 Russell S 300 65,000.00 ---------- Sum(Salary) 154,000.00
例: 単一のSELECT文での複数のWITH句の指定
次の文では、部門別に並んだ従業員の給与報告書を作成します。各部門の合計給与のサマリー行と、組織全体の合計給与のための最後のサマリー行を備えています。
SELECT name, dep_tno, salary FROM employee WITH SUM(salary) BY dept_no WITH SUM(salary);
返される結果は次のとおりです。
name dept_no salary ---------- ------- ----------- Peterson J 100 25,000.00 Moffit H 100 35,000.00 Jones M 100 50,000.00 Chin M 100 38,000.00 Greene W 100 32,000.00 ---------- Sum(Salary) 180,000.00 Leidner P 300 34,000.00 Phan A 300 55,000.00 Russell S 300 65,000.00 ---------- Sum(Salary) 154,000.00 Smith T 500 42,000.00 Clements D 700 38,000.00 ---------- Sum(Salary) 113,000.00 ---------- Sum(Salary) 851,000.00
例: WITH句とORDER BY句の組み合わせ
次の文では両方ともORDER BY句を使用して、各dept_noグループ内の従業員の名前を昇順でソートします。
SELECT name, dept_no, salary FROM employee ORDER BY name WITH SUM(salary) BY dept_no WITH SUM(salary); SELECT name, dept_no, salary FROM employee WITH SUM(salary) BY dept_no WITH SUM(salary) ORDER BY name;
返される結果は次のとおりです。
name dept_no salary ---------- ------- ----------- Chin M 100 38,000.00 Greene W 100 32,500.00 Jones M 100 50,000.00 Moffit H 100 35,000.00 Peterson J 100 25,000.00 ---------- Sum(Salary) 180,500.00 . . . . . . . . . Brangle B 700 30,000.00 Clements D 700 38,000.00 Smith T 700 45,000.00 ---------- Sum(Salary) 113,000.00 ---------- Sum(Salary) 851,100.00
いずれかのソート キー列に英大文字/英小文字が混在して入力された文字データが入っていた場合、WITH...BYまたはORDER BYから作成された結果は、CASESPECIFICオプションがその列に定義されていたかどうかと、そのセッションで作用中の照合によって異なり、結果は予測できません。ORDER BY句と<Teradata Vantage™ - SQLデータ定義言語 - 詳細トピック、B035-1184>を参照してください。
GROUP BY句とWITH句の組み合わせにより、意図しない結果セットが返されないように注意して文を記述します。次の例では、GROUP BY句とWITH句を組み合わせた記述にミスがあった場合に起こりうる問題について説明します。
次の文は、従業員の給与の総合計およびdept_no別の給与合計の報告書を作成します。
SELECT dept_no, SUM(salary) FROM employee GROUP BY dept_no WITH SUM(salary);
返される結果は次のとおりです。
dept_no Sum(Salary) ------- ----------- 100 180,500.00 300 143,000.00 500 268,000.00 600 146,600.00 700 113,000.00 ----------- Sum(Sa 851,100.00
予想したとおりに、WITH句はすべての部門の合計給与のサマリー行を作成します。サマリーの表題は、dept_no列の幅が狭いため切り捨てられます。
必要のないBY句がWITH句内に入っていると、以下のクエリー例の後のレポートに示されているとおり、各部門の給与合計の後に冗長なサマリー行が生成されます。
SELECT dept_no, SUM(salary) FROM employee GROUP BY dept_no WITH SUM(salary) BY dept_no; dept_no Sum(Salary) ------- ---------- 100 180,500.00 ---------- Sum(Sa 180,500.00 . . . 700 113,000.00 ----------- Sum(Sa 113,000.00
例: SELECT文のWITH ... BY句にあるスカラー サブクエリー
次に示す例では、BY句にスカラー サブクエリー(SELECT prod_name ..。)を指定しています。
SELECT SUM(amount) FROM sales_table AS s WITH AVG(amount) BY (SELECT prod_name FROM prod_table AS p WHERE p.prod_no = s.prod_no);