aspose file tools*
The moose likes JDBC and the fly likes Group By Expr Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Group By Expr" Watch "Group By Expr" New topic
Author

Group By Expr

Graham Thorpe
Ranch Hand

Joined: Mar 25, 2002
Posts: 265
Hi,
One question on oracle group by
heres the query. I get foll error
select cs.name, cd.cd_stat, cd.cd_mode2, cd.cd_pp,
cd.cd_dp, cd.cd_dpd, cd.cd_amount27, cd.cd_status4, l.ld_8, cd.cd_mode2
from c cf, c1 cd, c2 cs, log l
where cs.cs_no = cf.cf_no and
cd.cd_no = cf.cf_no and
l.ld_no = cf.cf_no and
cf.cf_flg_name = 'A' and
cf.cf_flg_val = 'A' and
cf.cf_stat = 'A' and
to_date (cf.cf_auth_dt,'dd/mm/yyyy') = (select to_date(today_date,'dd/mm/yyyy') from d) group by ld_8
Pls tell me whats wrong with above queyr as I get not a group by expression. What are the rules of the gruop by expressio n.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by glkrr reddy:
Hi,
One question on oracle group by
heres the query. I get foll error
select cs.name, cd.cd_stat, cd.cd_mode2, cd.cd_pp,
cd.cd_dp, cd.cd_dpd, cd.cd_amount27, cd.cd_status4, l.ld_8, cd.cd_mode2
from c cf, c1 cd, c2 cs, log l
where cs.cs_no = cf.cf_no and
cd.cd_no = cf.cf_no and
l.ld_no = cf.cf_no and
cf.cf_flg_name = 'A' and
cf.cf_flg_val = 'A' and
cf.cf_stat = 'A' and
to_date (cf.cf_auth_dt,'dd/mm/yyyy') = (select to_date(today_date,'dd/mm/yyyy') from d) group by ld_8
Pls tell me whats wrong with above queyr as I get not a group by expression. What are the rules of the gruop by expressio n.
Generally speaking, everything that is in your group by section has to appear in your field selection part. Also, the only extra select fields you can add are group level functions( count(*), max( field ), min( field ), etc. ).
according to your group by, you can only have id_8 in your selection part.
From the Oracle docs:
group_by_clause
Use the group_by_clause to group the selected rows based on the value of expr(s) for each row, and returns a single row of summary information for each group. If this clause contains CUBE or ROLLUP extensions, then superaggregate groupings are produced in addition to the regular groupings.
Expressions in the group_by_clause can contain any columns in the tables, views, and materialized views in the FROM clause, regardless of whether the columns appear in the select list.
Restrictions:
The group_by_clause can contain no more than 255 expressions.
You cannot specify LOB columns, nested tables, or varrays as part of expr.
The total number of bytes in all expressions in the group_by_clause is limited to the size of a data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
If the group_by_clause references any object columns, the query will not be parallelized.
ROLLUP
ROLLUP is an extension to the group_by_clause that groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions for each row, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values.


For example, given three expressions in the ROLLUP clause of the group_by_clause, the operation results in n+1 = 3+1 = 4 groupings.


Rows based on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.
See Also:
- GROUPING for an example
- Oracle8i Data Warehousing Guide

CUBE
CUBE is an extension to the group_by_clause that groups the selected rows based on the values of all possible combinations of expressions for each row, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.


For example, given three expressions in the CUBE clause of the group_by_clause, the operation results in 2n = 23 = 8 groupings. Rows based on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows.


See Also:
- GROUPING
- "CUBE Example" for an example
- Oracle8i Data Warehousing Guide.

HAVING
Use the HAVING clause to restrict the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause, Oracle returns summary rows for all groups.
Specify GROUP BY and HAVING after the where_clause and CONNECT BY clause. If you specify both GROUP BY and HAVING, they can appear in either order.

See Also: the syntax description of expr in "Expressions" and the syntax description of condition in "Conditions"

AND
Purpose
The GROUPING function is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE. These operations produce superaggregate rows that contain nulls representing the set of all values. You can use the GROUPING function to distinguish a null that represents the set of all values in a superaggregate row from an actual null.
The expr in the GROUPING function must match one of the expressions in the GROUP BY clause. The function returns a value of 1 if the value of expr in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the GROUPING function is Oracle NUMBER.
Example
In the following example, if the GROUPING function returns 1 (indicating a superaggregate row rather than a data row from the table), the string "All Jobs" appears instead of the null that would otherwise appear:
SELECT DECODE(GROUPING(dname), 1, 'All Departments',
dname) AS dname,
DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
FROM emp, dept
WHERE dept.deptno = emp.deptno
GROUP BY ROLLUP (dname, job);
DNAME JOB Total Empl Average Sa
--------------- --------- ---------- ----------
ACCOUNTING CLERK 1 15600
ACCOUNTING MANAGER 1 29400
ACCOUNTING PRESIDENT 1 60000
ACCOUNTING All Jobs 3 35000
RESEARCH ANALYST 2 36000
RESEARCH CLERK 2 11400
RESEARCH MANAGER 1 35700
RESEARCH All Jobs 5 26100
SALES CLERK 1 11400
SALES MANAGER 1 34200
SALES SALESMAN 4 16800
SALES All Jobs 6 18800
All Departments All Jobs 14 24878.5714
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Group By Expr