group by first character

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

From stackoverflow

• 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?

  yukondude : You beat me to it, fair and square. Almost identical query too.

  Pablo Santa Cruz : Hehehehehe... Yah. I voted you up anyway ;-)

• 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.

• 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?

• 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...

• 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);