Aggregate func
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Aggregate func

  1. #1
    Join Date
    Apr 2001
    Posts
    45

    Aggregate func

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    153
    Select
    substr(COLUMNNAME,1,1),
    count(*)
    from
    TABLENAME
    Group by
    substr(columnname,1,1)


    U r Done
    Vijay.s

  3. #3
    Join Date
    Apr 2001
    Posts
    45

    Question

    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 ?



  4. #4
    Join Date
    May 2000
    Posts
    58
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Apr 2001
    Posts
    45
    Thanks jmodic and victoria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width