Selecting the top customers from my DB

[sammry]

i have 2 tables as follow

sales (table1)
sales_id, userid(buyerid), product id, productname, orderid, status

users (table2)
userid, username, pass

Now, I need to display the top customer who makes regular purchase on my shopping site.

How can I achieve this?

I tried as

SELECT A.(*), B.username FROM sales A, users B WHERE A.userid=B.userid AND A.status='delivered';

Now, the same has to be looped and shown as top 10 buyers with the username, need to know how to loop too

Need experts advice on this, thanks

[blocblue]

Have you tried using an inner join, and ordering by the number of sales descending?

SELECT `u`.*, COUNT(`s`.*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON (`s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered')
ORDER BY `total_sales` DESC
LIMIT 10

[sammry]

Hi, cant i just select userid and username from the users table as the users table is too long and has 45 columns, so SELECT u.userid, u.username, COUNT(s.*).... should not do...

Meantime, will try your query and update you too... thanks

[sammry]

hi and also, I need to display where minimum of 10 or 15 purchases has to be made

[blocblue]

SELECT `u`.`userid`, `u`.`username`, COUNT(`s`.*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON (`s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered')
HAVING COUNT(`s`.`id`) >= 10
ORDER BY `total_sales` DESC
LIMIT 10

[sammry]

Hi, its not showing any result, how shoulk i loop this with total_sales

[urtrivedi]

its not showing any result

what do u mean
1) no rows showed?
2) query giving any error?

you may also try withoug 10 or 15 conition as given below

SELECT `u`.`userid`, `u`.`username`, COUNT(`s`.*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON (`s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered')
ORDER BY `total_sales` DESC
LIMIT 10

[sammry]

Yeah I had tried with out 10 or 15 earlier but no luck... may be am not querying the loop exactly. can you help me with the complete query with displaying the row results. Sorry for the trouble, so much confused as of now. Thanks for your help and appreciate your help.

[blocblue]

Can you run the query in PHP MyAdmin, or the MySQL console to confirm it's getting some results?

Then:

<?php

// Prepare and execute query
$sql = 'SELECT `u`.`userid`, `u`.`username`, COUNT(`s`.*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON (`s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered')
ORDER BY `total_sales` DESC
LIMIT 10';
$result = mysql_query($sql);

// Check if no results found
if(mysql_num_rows($result) == 0)
    die('No results');

// Iterate through results
while(($row = mysql_fetch_assoc($result)) {
    echo "User id: {$row['userid']}, Username: {$row['username']}<br />";
}

[sammry]

Hi, I ran this query in phpmyadmin and this worked, but above which you gave not fetched, it fetched wrong and with errors. as you can see the edits below.

SELECT `u`.`userid`, `u`.`username`, COUNT(*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON `s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered'
GROUP BY `u`.`userid`
ORDER BY `total_sales` DESC
LIMIT 10';

Now, I want to include that having count more than 10, thats killing me now

[blocblue]

Wrap the query in double quotes, not single quotes.

Have you added the HAVING COUNT(*) > 10 back into the query and tested it in PHP MyAdmin again?

[sammry]

HI,

SELECT `u`.`userid`, `u`.`username`, COUNT(*) AS `total_sales`
FROM `users` `u`
INNER JOIN `sales` `s` ON `s`.`userid` = `u`.`userid` AND `s`.`status` = 'delivered'
GROUP BY `u`.`userid`
HAVING (COUNT(`s`.id) >= 10)
ORDER BY `total_sales` DESC
LIMIT 10';

this is querying the properly, thanks for tweaking and appreciate your time and help. :)