create barcode using vb.net RETRIEVAL: MULTIPLE TABLES AND AGGREGATION in Java

Encoding Data Matrix ECC200 in Java RETRIEVAL: MULTIPLE TABLES AND AGGREGATION

In summary, if your query contains a GROUP BY clause, the SELECT clause is allowed to contain only group expressions. A group expression is a column name that is part of the GROUP BY clause, or a group function applied to any other column expression. See also Table 8-3 at the end of Section 8.6.

The previous sections showed examples of using standard GROUP BY clauses. You can also use some more advanced features of the GROUP BY clause. Here, we will look at GROUP BY CUBE and GROUP BY ROLLUP. Let s start with a regular GROUP BY example, shown in Listing 8-36. Listing 8-36. Regular GROUP BY Example select , from group by DEPTNO -------10 10 10 20 20 30 30 30 deptno, job count(empno) headcount employees deptno, job; JOB HEADCOUNT ---------- --------MANAGER 1 DIRECTOR 1 ADMIN 1 MANAGER 1 TRAINER 4 MANAGER 1 SALESREP 4 ADMIN 1

You get an overview with the number of employees per department, and within each department per job. To keep things simple, let s forget about department 40, the department without employees.

Notice what happens if you change the GROUP BY clause and add the keyword ROLLUP, as shown in Listing 8-37. Listing 8-37. GROUP BY ROLLUP Example select , from group by DEPTNO -------10 10 deptno, job count(empno) headcount employees ROLLUP(deptno, job); JOB HEADCOUNT -------- --------ADMIN 1 MANAGER 1

10 10 20 20 20 30 30 30 30

1 3 1 4 5 1 1 4 6 14

The ROLLUP addition results in four additional rows, marked with >>> and <<< in Listing 8-37 for readability. Three of these four additional rows show the head count per department over all jobs, and the last row shows the total number of employees.

You can also use the CUBE keyword in the GROUP BY clause. Listing 8-38 shows an example. Listing 8-38. GROUP BY CUBE Example select , from group by deptno, job count(empno) headcount employees CUBE(deptno, job);

DEPTNO JOB HEADCOUNT -------- -------- --------14 >>> ADMIN 2 >>> MANAGER 3 >>> TRAINER 4 >>> DIRECTOR 1 >>> SALESREP 4 10 3 10 MANAGER 1 10 DIRECTOR 1 10 ADMIN 1 20 5 20 MANAGER 1 20 TRAINER 4 30 6 30 MANAGER 1 30 SALESREP 4 30 ADMIN 1

This time, you get five more rows in the query result, marked in the same way with >>> and <<<, showing the number of employees per job, regardless of which department employs them.

Tip Both GROUP BY CUBE and GROUP BY ROLLUP are special cases of the GROUP BY GROUPING SETS syntax, offering more flexibility. You can also merge the results of different grouping operations into a single GROUP BY clause by specifying them in a comma-separated list. For more details, see Oracle SQL Reference.

The CUBE and ROLLUP keywords generate many null values in query results, as you can see in Listings 8-37 and 8-38. You can distinguish these system-generated null values from other null values; for example, to replace them with some explanatory text. You can use the GROUPING and GROUPING_ID functions for that purpose.

Listing 8-39 shows an example \of the GROUPING function. Listing 8-39. GROUPING Function Example select , deptno case GROUPING(job) when 0 then job when 1 then '**total**' end job , count(empno) headcount from employees group by rollup(deptno, job); DEPTNO -------10 10 10 10 20 20 20 30 30 30 30 JOB HEADCOUNT --------- --------ADMIN 1 MANAGER 1 DIRECTOR 1 **total** 3 MANAGER 1 TRAINER 4 **total** 5 ADMIN 1 MANAGER 1 SALESREP 4 **total** 6 **total** 14

Unfortunately, the GROUPING function can return only two results: 0 or 1. That s why the last two lines both show '**total**'.