group by first character

Question

hello friends i have a problem in oracle -sql.

i have a query please solve out it

i have a column first_name in employees table. i want to group my records according to first character of column first_name.

like if i have 26 name from (A-Z) in column first_name then there should be 26 group according to alphabates.

i tried following but its not working..

seklect employee_id, (substr(first_name,1,1)) as alpha from employees group by alpha;

name_which _starts_from employees
A 10
B 2
C 4
D 9
E 3
G 3
H 3
I 2
J 16
K 7
L 6
M 6
N 4
O 1
P 6
R 3
S 13
T 4
V 2
W 3

Answer 1

Your query is wrong, since you would need to perform some aggregation function on EMPLOYEE_ID if you want that to work.

Like:

select substr(first_name,1,1) as alpha, count(employee_id)
  from employees
 group by substr(first_name,1,1)

What exactly you are trying to accomplish?

Answer 2

You'll need to group by everything that is not an aggregate function, so you can't have employee_id in the SELECT projection. You also need to group by just the first character of the first_name. Something like this should work:

SELECT  SUBSTR(first_name, 1, 1) AS alpha, COUNT(*) AS employee_count
FROM    employees
GROUP   BY SUBSTR(first_name, 1, 1);

That would group by the first letter of the first name, and show the number of employees that fall into that group.

Answer 3

When you are grouping, all of the columns that appear in your select list that are not aggregated have to also appear in the "group by" clause (employee_id does not).

Could you clarify what it is you are trying to do?

Answer 4

I think i know what you are trying to do...

You should create a small reference table with a column 'letter' (letter, sort_order)

You should your query as

select l.letter, count(e.id) as employees from letter l left outer join employee e on l.letter = substr(e.first_name, 1,1)

the other answer posted will give you unexpected results when there are no employees with a specific letter in their name...

Answer 5

It almost sounds like you want 26 records returned with A, B, C as the first column and then a second column containing all the employee IDs in a delimited list. If so see question 468990 and/or this Ask Tom link. Something like (untested)

SELECT SUBSTR(first_name,1,1), TO_STRING( CAST( COLLECT( employee_id ) AS ntt_varchar2 ) ) AS empIDs
FROM   employees
GROUP  BY
SUBSTR(first_name,1,1);