Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: summation of group data

    hello oracle developers..
    Iam using coldfusion's cfml for report generation.
    In a report the record set returned by a query must be formatted in the following way.
    records r grouped on a column(say batchid).
    at the end of each recordset for a particular group the summary of a column values must be displayed.and the report should continue in the same format.

    (The database used is ORacle9i..)

    like..
    batchid---c1---c2---nofrec
    10---aa---bb---125
    10---zz-ss---35
    10---lll---dd---20
    total records---180
    20---ii---ee---55
    20--yy-qq-35
    total records---90
    .......
    ........
    Can something be done on oracle( I mean ..sql) end ..if so..
    please help me in doing this..
    thanks in advance

  2. #2
    Join Date
    Mar 2004
    Location
    Sydney
    Posts
    20
    I think you will find that grouping is usually done by the reporting tool I imagine cold fusion can do this. But if you want to get it from oracle try

    select batchid, sum(amount)
    from table_name
    group by batchid

    This will give you the sub totals by batch but you will still need to stick it into the report at the correct spot.

    or if you want to get the whole lot in one go try something like

    select itemid as "name", amount, batchid, 1 as "ord"
    from tablex
    union
    select batchid as "name", sum(amount), batchid, 2 as "ord"
    from tablex
    group by batchid
    order by 3,4

    column 1 and 2 is the data you want column 3 and 4 are for sorting purposes. nb the first query gives you the detail and the second gives you the sub total the orderby puts them together.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I think you will find that grouping is usually done by the reporting tool I imagine cold fusion can do this.
    yes indeed

    http://www.dbforums.com/showthread.php?threadid=987656


    sridharreddy_d, you posted the same question 5 times in different forums

    please don't do that


    Scoee, your "whole lot in one go" is excellent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89
    Originally posted by r937
    yes indeed

    http://www.dbforums.com/showthread.php?threadid=987656


    sridharreddy_d, you posted the same question 5 times in different forums

    please don't do that


    Scoee, your "whole lot in one go" is excellent
    Yes..Mr.r937..,I posted the same question to multiple forums coz it was bit urgent....but whats wrong in that...,let me know..
    Now..I could get the output format using cfml.
    thanks for the response ..

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    you could use analytics to have an ongoing summation.

    so it would look like:

    PHP Code:
    batchid---c1---c2---nofrec    total_per_group
    10    aa    bb    125    125
    10    zz    ss    35     160
    10    lll   dd    20     180

    20    ii    ee    55      55
    20    yy    qq    35      90 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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