use of group by clause in oracle apex

Question

First of all,I am new to Oracle that's why my question may be funny for experienced developers. Sorry for that in advance.

Ques. I have a table Test with 3 cols (ID, Name, Group_name)

Table

ID | Name | Group_name
1  | name1 | group 1 
2  | name2 | group 1  
3  | name3 | group  1 
4  | name4 | group 2
5  | name5 | group 2

I want to create a select list in oracle apex .

-Select-

(Group 1)
Name 1
Name 2
Name 3
(Group 2)
Name 4
Name 5

in which Group1 & Group2 are only for display and are not selectable.

I tried:

SELECT NAME, ID
FROM TEST
GROUP BY GROUP_NAME

gives error:

not a group by expression

You need to put GROUP_NAME into the SELECT. Check this link : https://stackoverflow.com/questions/7434657/how-does-group-by-work — Zeki Gumus, Dec 10 '18 at 13:13

score 1 · Answer 1 · answered Dec 10 '18 at 21:06

First, your query isn't valid SQL. That won't work in any environment, whether Apex or sqlplus. But that's not important becuase...

Second, since you want the group names to be displayed and not selectable, the HTML you're trying to generate would look something like:

<select>
    <optgroup label="Group 1">
        <option>Name 1</option>
        <option>Name 2</option>
        <option>Name 3</option>
    </optgroup>
    ....

Apex Select item types don't support <optgroup> (unless they've added that in recent versions).

I think your options are to write a plugin, but that's a fairly advanced topic, or to use one that someone else has already made. The Select2 Apex Plugin would work for your purposes. See "Option Grouping" lower down on that page.

score 0 · Answer 2 · answered Dec 10 '18 at 21:28

As far as I can tell, in native Apex you can't affect whether some select list elements will not be selectable (which is what you want with group names).

However, you can create a select list query which displays what you want. Here's an example:

SQL> with test (id, name, group_name) as
  2    (select 1, 'name1', 'group 1' from dual union all
  3     select 6, 'name2', 'group 1' from dual union all
  4     select 9, 'name3', 'group 1' from dual union all
  5     --
  6     select 2, 'name4', 'group 2' from dual union all
  7     select 4, 'name5', 'group 2' from dual
  8    ),
  9  inter as
 10    (-- Group names; ID is a negative value to make sure it is displayed first
 11     select -row_number() over (order by group_name) id,
 12       '(' || initcap(group_name) ||')' name, group_name
 13     from test
 14     group by group_name
 15     union
 16     -- members of the group
 17     select id, name, group_name
 18     from test
 19    )
 20  select name display_value,
 21         id   return_value
 22  from inter
 23  order by group_name, id;

DISPLAY_V RETURN_VALUE
--------- ------------
(Group 1)           -1
name1                1
name2                6
name3                9
(Group 2)           -2
name4                2
name5                4

7 rows selected.

SQL>

use of group by clause in oracle apex

2 Answers2