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?