-
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?
-
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 01:01 AM.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|