MySQL COUNT and merge duplicate records

Question

I'm trying to run a query which counts the number of orders each user has made. Unfortunately, the system was not set up well initially and there are a number of duplicate customer records (using the same email address).

I currently have the following simple query:

SELECT 
u.id,
u.name
u.email,
COUNT(o.id) AS num_orders
FROM users u
INNER JOIN orders o
ON o.user_id = u.id
GROUP BY u.id
ORDER BY u.id

Is there anything I can do to it to merge the counts for duplicate users?

Any advice appreciated.

Thanks

podiluska · Accepted Answer · 2012-10-11 08:33:28Z

1

SELECT  
    u.email, 
    COUNT(o.id) AS num_orders 
FROM users u 
    INNER JOIN orders o 
        ON o.user_id = u.id 
GROUP BY u.email
ORDER BY u.email

Incidentally, while mysql allows you to do GROUP BY u.id and omit the other non-aggregate fields in your query, it is not good nor standard SQL and should be avoided.

answered Oct 11, 2012 at 8:33

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

Collectives™ on Stack Overflow

MySQL COUNT and merge duplicate records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related