Query in getting multiple duplicate rows in SQL Server

Question

I have 2 tables Table1 and Table2 in which I want to get the total count of duplicate rows:

Expected output:

Query tested:

SELECT 
    t1.name,
    t1.duplicates,
    ISNULL(t2.active, 0) AS active,
    ISNULL(t3.inactive, 0) AS inactive 
FROM 
    (SELECT
         t1.name, COUNT(*) AS duplicates 
     FROM 
         (SELECT c.name  
          FROM table1 c 
          INNER JOIN table2 as cd on cd.id = c.id)) t1  
    GROUP BY 
        name 
    HAVING 
        COUNT(*) > 1) t1  
LEFT JOIN 
    (SELECT c.name, COUNT(*) AS active 
     FROM table1 c 
     WHERE name IN (SELECT c.name FROM table1 c) 
     GROUP BY c.name AND status = 'Active' 
     GROUP BY name) t2 ON t1.name = t2.name  
LEFT JOIN 
    (SELECT c.name, COUNT(*) AS inactive 
     FROM table1 c  
     WHERE name IN (SELECT c.name FROM table1 c GROUP BY c.name)  
       AND status = 'InActive'
     GROUP BY name) t3 ON t1.name = t3.name 
ORDER BY 
    name

It is still returning duplicate rows and I'm unable to get the id and creator column

Answer 1

If you would pardon subquery and left join, i'd suggest the following query:

select b.*,
       count(creator) as creator_count
 from
  (select a.mainid,
          a.name,
          sum(case when a.status = "active"
              then 1 else 0 end) as active_count,
          sum(case when a.status = "inactive"
              then 1 else 0 end) as inactive_count,
          count(a.name) as duplicate_count
    from table1 as a
    group by a.name
    having count(a.name) > 1) as b
  left join table2 as c
  on b.mainid = c.mainid
  group by c.mainid
  having count(c.creator) > 1

rather than forcing our way to join the two table directly. First, derive the information we can get from the Table1 then join it with the Table2 to get the creator count.

SQL Fiddle: http://sqlfiddle.com/#!9/4daa19e/28