15

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
  • 159,146
  • 25
  • 197
  • 199
user982853
  • 2,470
  • 14
  • 55
  • 82

3 Answers3

33

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
Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 1
    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. – user982853 Dec 07 '11 at 03:46
  • It's possibile to add the date of the last purchase to the first query? – ipel Jul 25 '16 at 09:28
  • 1
    @ipel Sure. You can add something like max(p.purchase_date) to the select list. – Asaph Jul 25 '16 at 09:32
  • If you're wondering if LEFT JOIN and LEFT OUTER JOIN are the same, they are according to this SO post: https://stackoverflow.com/questions/15425740/are-left-outer-joins-and-left-joins-the-same – AdrianCR Sep 26 '18 at 01:25
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.

kz3
  • 785
  • 2
  • 10
  • 23
  • 2
    An outer join would be advisable so that you can include contacts with zero purchases too. – Asaph Dec 07 '11 at 02:57
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
z-index
  • 409
  • 8
  • 14
  • 2
    An outer join would be advisable so that you can include contacts with zero purchases too. – Asaph Dec 07 '11 at 02:56