Can't Grouping Data in Oracle

Question

I have an Oracle query that currently display all of my data

$qu = 'SELECT * FROM baan.tcbpur012777 WHERE T$RQNO LIKE :bagian GROUP BY tcbpur012777.T$RQNO ORDER BY T$RQNO OFFSET '.$start.' ROWS FETCH NEXT '.$perPage.' ROWS ONLY';
$r = oci_parse($conn, $qu);
oci_bind_by_name($r,':bagian',$bag);
oci_execute($r);
$n = oci_fetch_all($r, $q);

What i wanna do is group the duplicated data into one,But Every time i use group by syntax, warning will appear and make my syntax invalid.

Warning: oci_fetch_all(): ORA-24374: define not done before fetch or execute and fetch in

Can anybody please tell me what's wrong with my syntax?? Thanks in advance

*"What i wanna do is group the duplicated data into one"* -- `GROUP BY` does not do this. It doesn't remove duplicate rows, it doesn't even returns rows from the table (and `SELECT * ... GROUP BY` is invalid SQL). `GROUP BY` fetches rows from the table, put them in groups by the `GROUP BY` expressions then it **computes** the `SELECT` expressions using the values from each group. It **generates** one record from each group. — axiac, Nov 01 '17 at 07:01
okay, thanks for the explanation, do you have any suggestion for my problem?? Usually in MySQL, i use GROUP BY to do that — Rico Shurui, Nov 01 '17 at 07:03
Before version 5.7.5, MySQL used to accept invalid `GROUP BY` queries but it reserved its right to [return indeterminate values](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) for the `SELECT` expressions that are not calls of [aggregate (`GROUP BY`) functions](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html) and are not also present in the `GROUP BY` clause (all the columns of `tcbpur012777` except for `T$RQNO` in your query). It doesn't accept invalid `GROUP BY` queries since 5.7.5. Oracle also doesn't accept them. — axiac, Nov 01 '17 at 07:20
If you want to ignore duplicates you can try to `SELECT DISTINCT col1, col2, ... FROM baan.tcbpur012777...`. You have to put into the query each column you need (I guess two rows you consider duplicates are not equal on all columns, they have different values for the `PK`). You don't need `GROUP BY`. — axiac, Nov 01 '17 at 07:22

score 0 · Answer 1 · answered Nov 01 '17 at 09:37

You are doing:

SELECT   *
FROM     table_name
GROUP BY column_name

You need to either include all the columns in the GROUP BY clause or, for each column not in the GROUP BY clause you need to use an aggregation function to aggregate it over the group. Assuming your table has more than one column then SELECT * will get all those columns but GROUP BY column_name is only grouping by one of the columns and the other columns are not part of an aggregation so it will throw an ORA-00979 not a group by expression exception.

Something like:

SELECT   T$RQNO,
         MAX( Col1 ) AS MaxCol1,
         MIN( Col2 ) AS MinCol2,
         LISTAGG( Col3, ',' ) WITHIN GROUP ( ORDER BY Col4 ) AS ListOfCol3
FROM     baan.tcbpur012777
WHERE    T$RQNO LIKE :bagian
GROUP BY T$RQNO
ORDER BY T$RQNO
OFFSET     '.$start.' ROWS
FETCH NEXT '.$perPage.' ROWS ONLY

Can't Grouping Data in Oracle

1 Answers1