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
U r Done
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 ?
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)
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.
The sql ( from vbaskar ) is combined with this with an union all and the counts are summed up.
Here is another, slightly different solution:
SELECT x.character, COUNT(emp.empno)
----(SELECT CHR(ROWNUM - 1 + ASCII('A')) character
-------WHERE ROWNUM <= ASCII('Z') - ASCII('A') + 1
WHERE x.character = SUBSTR(emp.ename(+),1,1)
GROUP BY x.character;
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Thanks jmodic and victoria
Click Here to Expand Forum to Full Width