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

You need to explain to us how to obtain the second required result from the sample data. What is the rules / logic ? — Squirrel
– Squirrel, Commented Mar 23, 2022 at 13:43
You have obfuscated the data so much that it is meaningless and difficult to understand what you are trying to do here. Providing tables and sample data in a consumable format is lightyears betters than images. — Sean Lange
– Sean Lange, Commented Mar 23, 2022 at 14:35

Bagus Tesa · Accepted Answer · 2022-03-23 14:49:06Z

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

answered Mar 23, 2022 at 14:49

Bagus Tesa

1,7152 gold badges24 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prince Jkc Over a year ago

Thanks for the idea it help a lot, however I haven't included the mainid on my query because I'm getting "is not contained in either an aggregate function or the GROUP BY clause"

Collectives™ on Stack Overflow

Query in getting multiple duplicate rows in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related