Example: Specifying a Detail Line for Each Employee and a Subtotal Line for Each Department
You can use the following statement to generate a departmental salary report that contains a detail line for each employee and a subtotal line for each department:
SELECT name, dept_no, salary FROM employee WITH SUM(salary) BY dept_no;
The result returned is as follows:
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
Example: Specifying Multiple WITH Clauses in a Single SELECT Statement
The following statement generates a report of employee salaries ordered by department, with a summary line of total salaries for each department and a final summary line of total salaries for the entire organization:
SELECT name, dep_tno, salary FROM employee WITH SUM(salary) BY dept_no WITH SUM(salary);
The result returned is as follows:
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
Example: Combining WITH and ORDER BY Clauses
Both of the following statements use an ORDER BY clause to sort employee names in ascending order in each dept_no group.
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;
The result returned is as follows.
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
If any sort key column contains character data that was entered in mixed case, the results produced from WITH ... BY or ORDER BY can be unexpected, depending on whether the CASESPECIFIC option was defined on the column and the collation in effect for the session. See ORDER BY Clause and Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.
You must code your statements carefully to avoid returning unintended result sets from combining GROUP BY and WITH clauses. The next example illustrates a possible problem with careless coding of combined GROUP BY and WITH clauses.
The following statement is intended to generate a report of salary totals by dept_no with a grand total of employee salaries:
SELECT dept_no, SUM(salary) FROM employee GROUP BY dept_no WITH SUM(salary);
The result returned is as follows:
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
As would be expected, the WITH clause produces a summary line of total salaries for all departments. The summary title is truncated because of the narrow width of the dept_no column.
If a WITH clause contains an unnecessary BY phrase, then a redundant summary line is generated following each department salary total as seen in the report following this example query:
SELECT dept_no, SUM(salary) FROM employee GROUP BY dept_no WITH SUM(salary) BY dept_no;
Result:
dept_no Sum(Salary) ------- ---------- 100 180,500.00 ---------- Sum(Sa 180,500.00 . . . 700 113,000.00 ----------- Sum(Sa 113,000.00
Example: Scalar Subquery in the WITH ... BY Clause of a SELECT Statement
The following example specifies a scalar subquery (SELECT prod_name…) in the BY clause.
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);