Find The MAX of SUM in MySQL

Question

good day guys currently I'm making PHP(inventory management) for my items, in my system customers can buy items , I save customer ID and number of sold Items in one table (called it Sell table) in MySQL , now I want to show the best customer.(who bought the most items in my system) I wrote a code to find out how many items each customer bought ,but I don't know how I can show the best customer see this code :

$test= "SELECT c_id, SUM(n_sell) FROM sell GROUP BY c_id "; 
$resut = mysql_query($test) or die(mysql_error());
while($t = mysql_fetch_array($resut)){
    echo "Number of sold: ". $t['SUM(n_sell)'] ." to". $t['c_id'] .":id of customer";
    echo "<br />";
}

It shows all of my customers with numbers of sold items to them, I need to show the specific customer who has the maximum number of sell item . for example, this is my result :

Number of sold: 11 to 2 :id of customer Number of sold: 103 to 3: id of customer

but the thing I want is just show :

Number of sold :103 to 3 :id of customer

I hope you guys can get me, thanks.

bazzilic · Accepted Answer · 2012-03-24 10:28:10Z

4

Try this:

$test = "SELECT c_id, MAX(Bought) AS MaxBought FROM (SELECT c_id, SUM(n_sell) AS Bought FROM sell GROUP BY c_id) AS tmp HAVING MAX(Bought) = tmp.Bought"; 
$resut = mysql_query($test) or die(mysql_error());
while($t = mysql_fetch_array($resut)){
    echo "Number of sold: ". $t['MaxBought'] ." to". $t['c_id'] .":id of customer";
    echo "<br />";
}

Here is the SQL query alone for easier understanding:

SELECT c_id, MAX(Bought) AS MaxBought
FROM (SELECT c_id, SUM(n_sell) AS Bought
      FROM sell
      GROUP BY c_id) AS tmp
HAVING MAX(Bought) = tmp.Bought

answered Mar 24, 2012 at 10:28

bazzilic

8282 gold badges8 silver badges23 bronze badges

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

1 Comment

bazzilic Over a year ago

In case there are several best customers with equal amount of bought items, this code will show all of them. The one by hjpotter92 won't.

hjpotter92 · Accepted Answer · 2012-03-24 10:18:00Z

1

There are two methods for that(which I can think of). Both need change in your SQL query.

Sol. 1

$test= "SELECT c_id, MAX( SUM(n_sell) ) FROM sell GROUP BY c_id ";

Sol. 2

$test= "SELECT c_id, SUM(n_sell) FROM sell GROUP BY c_id ORDER BY SUM(n_sell) DESC LIMIT 1";

The remaining section of your code may remain the same.

answered Mar 24, 2012 at 10:18

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

2 Comments

sahapersian Over a year ago

Thanks , I wrote the first method before,It dosen't work in my system ,don't know why , but second one is new for me , thanks alot. It works....

ypercubeᵀᴹ Over a year ago

The MAX(SUM( )) will not work as you expect. Please edit your answer.

undefined · Accepted Answer · 2012-03-24 10:18:24Z

1

Use ORDER BY .

answered Mar 24, 2012 at 10:18

undefined

2,0933 gold badges26 silver badges48 bronze badges

Collectives™ on Stack Overflow

Find The MAX of SUM in MySQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related