MYSQL count related records one query

Question

So i have a mysql query that queries a table "contacts" each contact then has purchases. Purchases are in a related table. I want to display each contacts name with the number of purchases they have made to the right like this:

Adam(1)
Mike(8)
Steve(3)

My current sql looks like this:

SELECT * FROM contacts ORDER BY contacts.name ASC"

and my current table looks like this:

Adam
Mike
Steve

In order to pull the count of the related (purchases) table into the current table i know i have to join the "purchases" table some how and then use the GROUP BY and count() function but i am not sure how to construct this sql statement. Can someone help me.

Again all i am trying to do is list a table (contacts) and count it's related records (purchases) and have it look like this:

Adam(1)
Mike(8)
Steve(3)

Thank you so much for any help.

Asaph · Accepted Answer · 2011-12-07 02:58:01Z

34

Assuming the purchases table has a foreign key to the contacts table called contact_id, a query something like this should work:

SELECT c.name, count(p.contact_id)
FROM contacts AS c
LEFT OUTER JOIN purchases AS p
ON p.contact_id = c.contact_id
GROUP BY c.contact_id
ORDER BY c.name ASC

This query will put the count in a separate column, which I recommend. If you must format it the way you indicated with the parentheses after the name, you can use MySQL's concat() function. Something like this:

SELECT concat(c.name, '(', count(p.contact_id), ')') AS Name_And_Count_Together
FROM contacts AS c
LEFT OUTER JOIN purchases AS p
ON p.contact_id = c.contact_id
GROUP BY c.contact_id
ORDER BY c.name ASC

edited Dec 7, 2011 at 2:58

answered Dec 7, 2011 at 2:52

Asaph

163k25 gold badges204 silver badges204 bronze badges

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

4 Comments

user982853 Over a year ago

Thank you so much, that worked like a charm. Glad i posted in here bc i would have never figured that out on my own. Thank you again.

ipel Over a year ago

It's possibile to add the date of the last purchase to the first query?

Asaph Over a year ago

@ipel Sure. You can add something like max(p.purchase_date) to the select list.

AdrianCR Over a year ago

If you're wondering if LEFT JOIN and LEFT OUTER JOIN are the same, they are according to this SO post: stackoverflow.com/questions/15425740/…

user1080894 · Accepted Answer · 2011-12-07 02:51:59Z

1

  SELECT contacts.name, COUNT(*) 
    FROM contacts, purchases 
   WHERE contacts.name = purchases.name
GROUP BY purchases.key
ORDER BY contacts.name

Replace .name in the WHERE clause with the key you are using to identify records.

answered Dec 7, 2011 at 2:51

user1080894

1 Comment

Asaph Over a year ago

An outer join would be advisable so that you can include contacts with zero purchases too.

z-index · Accepted Answer · 2011-12-07 02:50:57Z

0

The required query would be something like this (I don't know your schema):

SELECT c.name, count(p.id) FROM contacts c
JOIN purchases p ON (c.id = p.id)
GROUP BY p.id
ORDER BY contacts.name ASC

answered Dec 7, 2011 at 2:50

z-index

3898 silver badges14 bronze badges

1 Comment

Asaph Over a year ago

An outer join would be advisable so that you can include contacts with zero purchases too.

Collectives™ on Stack Overflow

MYSQL count related records one query

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related