Oracle SQL - Group and Detail records

Question

I am trying to build an Oracle SQL query, that would give me the grouped by row along with the rows that make up the group when the count is greater than 1. Please see the below for example and the SQL query that does the grouping. Any help or suggestions would be greatly appreciated.

For example using the below dataset -

======================
 ID | NAME |  AUTHOR
======================
 2  | Abc  | John 
 6  | Abc  | John 
 3  | Xyz  | Mike 
 4  | Abc  | Mike 
 5  | Xyz  | John 
 1  | Abc  | Mike 
 7  | PQR  | Raj 


Expected Result - 
===========================
ID | NAME | AUTHOR | COUNT
===========================
   | Abc  |        |  4
2  | Abc  |  John  |    
6  | Abc  |  John  |    
4  | Abc  |  Mike  |    
1  | Abc  |  Mike  |    
   | PQR  |        |  1
   | Xyz  |        |  2
3  | Xyz  |  Mike  |    
5  | Xyz  |  John  |    


SELECT  NAME, COUNT(NAME) from (
SELECT 2 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION 
SELECT 6 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION
SELECT 3 as ID, ' Xyz  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 4 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 5 as ID, ' Xyz  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION
SELECT 1 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 7 as ID, ' PQR  ' as NAME, ' Raj ' as AUTHOR FROM DUAL)
GROUP BY NAME
ORDER by NAME;

Answer 1

The data grouped by name with the count of author:

SELECT NULL id, name, NULL author, count(author) "count"
FROM myTable
GROUP BY name

plus the details when there is more then one author:

SELECT id, name, author, NULL "count"
FROM mytable
WHERE name NOT IN (SELECT name FROM myTable GROUP BY name HAVING count(1) = 1)

Put those together with a little order to get the wanted result

SELECT id, name, author, NULL "count"
FROM mytable
WHERE name NOT IN (SELECT name FROM myTable GROUP BY name HAVING count(1) = 1)
UNION ALL
SELECT NULL id, name, NULL author, count(author) "count"
FROM myTable
GROUP BY name
ORDER BY 2, 4, 1, 3

Answer 2

select
    id, name, author, decode(grouping_id(id, name), 2, count(*)) count
from
    books
group by
    rollup(name, (author, id))
having
    grouping_id(id, name) != 3
order
    by name, id nulls first

Answer 3

SQL> with t as (
  2  SELECT 2 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
  3  UNION
  4  SELECT 6 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
  5  UNION
  6  SELECT 3 as ID, ' Xyz  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
  7  UNION
  8  SELECT 4 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
  9  UNION
 10  SELECT 5 as ID, ' Xyz  ' as NAME, ' John ' as AUTHOR FROM DUAL
 11  UNION
 12  SELECT 1 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
 13  UNION
 14  SELECT 7 as ID, ' PQR  ' as NAME, ' Raj ' as AUTHOR FROM DUAL)
 15  select id, name, author, count#
 16  from (
 17  select t.id, t.name, t.author, decode(grouping(id),1,count(*),null) count#,
 18  count(*) over (partition by name) cn, grouping(id) gid
 19  from t
 20  group by grouping sets((id,name,author),(name))
 21  )
 22  where (cn != 2 or count# is not null)
 23  order by name, gid desc, author
 24  /

        ID NAME   AUTHOR     COUNT#                                             
---------- ------ ------ ----------                                             
            Abc                   4                                             
         2  Abc    John                                                         
         6  Abc    John                                                         
         4  Abc    Mike                                                         
         1  Abc    Mike                                                         
            PQR                   1                                             
            Xyz                   2                                             
         5  Xyz    John                                                         
         3  Xyz    Mike