Decode or translate with rollup
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Decode or translate with rollup

  1. #1
    Join Date
    Jul 2005
    Posts
    4

    Decode or translate with rollup

    I am trying to get this output:

    Code:
    Writer     Sum
    ------     -----
    John       8
    Anne       6
    Total:  2  14
    I have made the following code which gets the correct figures but I canīt get rid of the rowresults on the second column.

    Code:
    SELECT nvl((a.name),'Total:') AS Writer count(DISTINCT a.name), count(b.isbn) AS Sum
    FROM author a, book b
    WHERE a.id = b.author_id
    GROUP BY ROLLUP(a.name)
    I have tried with decode(count(DISTINCT a.name),1,null) and translate(count(DISTINCT a.name),1,null). Then I get blank rows but no summary for the second column.

    I also want to have the columnname blank, is that possible?

  2. #2
    Join Date
    May 2005
    Posts
    31
    how about query like this ?

    select Writer, decode(groupid,1,cntname,null), summ from (SELECT nvl((a.name),'Total:') AS Writer count(DISTINCT a.name) cntname, count(b.isbn) AS Summ, grouping (a.name) groupid
    FROM author a, book b
    WHERE a.id = b.author_id
    GROUP BY ROLLUP(a.name))

    /
    Last edited by Bonker; 07-06-2005 at 02:01 AM.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #3
    Join Date
    Jul 2005
    Posts
    4
    Thank you it worked great, altough I donīt really understand how itīs built up yet.

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