mysql + issues with JOIN query

Question

Ok folks, im sorry to ask about this since ive seen a couple of mysql JOIN examples but i seem to be unable to get it to work.

"sales"

----------------------
idcustomer | datecode 
----------------------
 1         | 20120503 
 1         | 20120503 
 1         | 20120503 
 2         | 20120503 
 3         | 20120503

I want to know who is the top buyer.... in terms of HOW MANY TIMES a customer buys something from me on a especific day (yes i use some weird format for date i know, please nevermind that)...so i do:

SELECT idcustomer, COUNT(idcustomer) FROM sales WHERE datecode = 20120503 GROUP BY idcustomer ORDER BY COUNT(idcustomer) DESC

AND I GET:

-----------------------------
idcustomer | Count(idcustomer)
-----------------------------
 1         | 3
 2         | 1
 3         | 1

Question is... since i also have the table:

"customer"

----------------------
| name | id_customer |
----------------------
 Jess  | 1
 Matt  | 2
 Perry | 3

And below is what i would like to achieve.... how to do so?

---------------------------------------------
customer.name | idcustomer | Count(idcustomer)
---------------------------------------------
 Jess         | 1          | 3
 Matt         | 2          | 1
 Perry        | 3          | 1

Mark Byers · Accepted Answer · 2012-06-06 05:39:59Z

1

SELECT customer.name, idcustomer, COUNT(idcustomer)
FROM sales
JOIN customer
ON sales.idcustomer = customer.id_customer
WHERE datecode = 20120503
GROUP BY idcustomer
ORDER BY COUNT(idcustomer) DESC

See it working online: sqlfiddle

answered Jun 6, 2012 at 5:39

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

1 Comment

Khul Over a year ago

Thanks for the replay. Loved the sqlfiddle.

Kshitij · Accepted Answer · 2012-06-06 05:42:55Z

0

You will need to do it this way -

SELECT idcustomer, c.name, COUNT(idcustomer) 
FROM sales s inner join customer c on (s.idcoustomer=c.id_customer)  
WHERE datecode = 20120503 
GROUP BY idcustomer, c.name  
ORDER BY COUNT(idcustomer) DESC

answered Jun 6, 2012 at 5:42

Kshitij

8,6642 gold badges29 silver badges34 bronze badges

Comments

Sashi Kant · Accepted Answer · 2012-06-06 05:53:10Z

0

Hope this will help ::

Select cust_ref.idcustomer, cust_ref.COUNT(idcustomer), customer.name
from 
(SELECT idcustomer, COUNT(idcustomer) FROM sales WHERE datecode = 20120503 
 GROUP BY idcustomer) cust_ref
 inner join customer on (sales.idcustomer = customer.id_customer)
ORDER BY COUNT(idcustomer) DESC

answered Jun 6, 2012 at 5:53

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Comments

vedmed · Accepted Answer · 2012-06-06 06:01:50Z

0

SELECT 
  t1.name,t2.count_buy,t2.idcustomer 
   FROM customer as t1,
 (SELECT 
    idcustomer,COUNT(idcustomer) as count_buy 
       FROM `sales`  
        WHERE datecode = 20120503
        GROUP BY idcustomer 
         ORDER BY COUNT(idcustomer) DESC) t2
  WHERE 
   t1.idcustomer =t2.idcustomer

answered Jun 6, 2012 at 6:01

vedmed

3552 gold badges8 silver badges16 bronze badges

1 Comment

Khul Over a year ago

This method i have not seen before. I shall investigate further into it. ty

Collectives™ on Stack Overflow

mysql + issues with JOIN query

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related