CODEWITHSUNDEEP
mysql
Fabio Manniti
Fabio Manniti

Reputation: 171

mySQL find duplicates in table

I have two tables: users and orders.

Sometimes I see users that are subscribed twice with different email but same first name, last name and VAT number. I would like to select all users that have registered twice and both account have at least one order. Then I want to see the detail for both accounts.

If I do something like

SELECT first_name, last_name, VAT, count(distinct o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE 1=1
AND o.id IS NOT NULL
GROUP BY first_name, last_name, VAT
HAVING count(distinct u.id) > 1

I can select double accounts with orders but I can only see the sum of orders in both account. Instead I would like to see two lines for each one of them and see how many orders each line has, and possibly the id of each account

So:

first name last name vat id orders
Robert Smith 11111 id1 5
Robert Smith 11111 id2 3

Upvotes: 0

Views: 42

Answers (1)

Akina
Akina

Reputation: 42611

Test this:

WITH cte AS (
    SELECT first_name, last_name, VAT, id,
           COUNT(DISTINCT id) OVER (PARTITION BY first_name, last_name, VAT) cnt
    FROM users
)
SELECT cte.first_name, cte.last_name, cte.VAT, cte.id, COUNT(*) orders
FROM cte
JOIN orders o ON cte.id = o.user_id
WHERE cte.cnt > 1
GROUP BY cte.first_name, cte.last_name, cte.VAT, cte.id
ORDER BY cte.first_name, cte.last_name, cte.VAT, cte.id

Upvotes: 1

Related Questions