Aggregate func

[mayse]

I have a table with a col that stores names. What I would like to do is determine the sum of records grouped by the first letter of the name. i.e give me a sum of all names starting with A,B,C etc...

The o/p should look like
Name: Count:
A 10
B 45
C 0
D 93

etc


Thanks

[vbaskar]

Select
substr(COLUMNNAME,1,1),
count(*)
from
TABLENAME
Group by
substr(columnname,1,1)


U r Done

[mayse]

Vbaskar,

Thanks for your response. It almost does what I want, except for the letters which have a count of 'zero'.
That is the catch....

I suspect I'll have to use a temp table to output the results of your query and then join that with a table containing all the letters from the alphabet. Can you think of a better way ?

[Victoria]

mayse,
Here is the sql you need.

select s, sum(cnt) from (
select substr(name,1,1) s , count(*) cnt from x
group by substr(name,1,1)
union all
select substr(alph,z.r,1) s , 0 cnt
from ( select 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz' alph from dual ) y ,
( select rownum r from tab where rownum <= 52 ) z
)
group by s

Assumption : You should have atleast 52 tables in your schema. The sql is basically very simple. The select which is below union all outputs all the 52 alphabets with zeroes like this.
A 0
B 0
.. ..
The sql ( from vbaskar ) is combined with this with an union all and the counts are summed up.

[jmodic]

Here is another, slightly different solution:

SELECT x.character, COUNT(emp.empno)
FROM emp,
----(SELECT CHR(ROWNUM - 1 + ASCII('A')) character
-------FROM all_objects
-------WHERE ROWNUM <= ASCII('Z') - ASCII('A') + 1
-----) x
WHERE x.character = SUBSTR(emp.ename(+),1,1)
GROUP BY x.character;

[mayse]

Thanks jmodic and victoria