Need help with query

Hello:

I am trying to get a mysql query together an not quite sure how to apply it, whether it is with distinct, join and all. What I am looking is to get the ID, qty ( number of users ), max and min number. Can anyone help me with this please.


Name ID Name
123ABC Engine
649DFA Transmission
854UDF Window


User ID User Name User Amount
123ABC Paul 300
123ABC John 190
649DFA Ian 500
123ABC Mary 210
123ABC Mark 225
649DFA Steve 360




Required Result:

ID Qty Max Mix
123ABC 4 300 190
649DFA 2 500 360

 

are they all in the same column?

 

No, the field names bunched together when I sent it.

Name ID, Name


User ID, User Name, User Amount

 

$result = mysql_query("SELECT * FROM ID");
while($row = mysql_fetch_array($result))
{
$userid= $row['User ID'];
$username = $row['User Name'];
$User Amount = $row['User Amount'];
}

echo $userid . $username . $User Amount;


try that code

 

We can do it in a single query..please check you PM

 

From your example it appears that you don't need the information on the first table. The solution is as simple as:

SELECT B.user_id AS id, COUNT(B.user_name) AS Qty, Max(B.user_amount) AS Max, Min(B.user_amount) AS Min
FROM B
GROUP BY (id)
ORDER BY id;

Daniel