Adding Results Then Selecting Top Results

overyde · Jan 27, 2008

Hey All,
It's been a while...
I'm looking to go through a table of sales which has a reference to the sales person for that sale. There is another table with details of the sales person.

What I need to do is add up the amount of sales each person did (amount of individual sales not value of sales) then select the top 10 sales people and display their names. Any ideas. I'll do a basic structure of the tables below:
----------------------
|Sales Table |
----------------------
id | sale desc|saleperson
--------------------------
1 | Sale1 |person1
2 | Sale2 |person2
3 | Sale3 |person1

-----------------------
| Sales People |
------------------------
1 | Joe | Smith
2 | Bob | Rogers

Result:
1 Joe Smith with 2 sales
2 Bob Rogers with 1 sale

Reality is built on a foundation of dreams.

jpadie · Jan 27, 2008

very roughly the query would look something like this

Code:

select concat(sp.firstname, ' ', sp.lastName) as salesPerson, count(s.id) as numSales from SalesPeople as sp JOIN (salesTable as s) on sp.id = s.salesperson order by numSales DESC LIMIT 10

but that's about as much sql type help that is reasonable in a non-sql forum (and it's probably wrong as i'm not a sql expert). for more details on the query check out the mysql forum.

then process the query results in the normal php manner

Code:

$results = mysql_query($query) or die(mysql_error());
$counter = 0;
while ($row = mysql_fetch_assoc($results)){
 $counter++;
 echo <<<HTML
<div>{$counter}. {$row['salesPerson']} with {$row['numSales']} sales</div>

HTML;
} //end the while loop

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Adding Results Then Selecting Top Results

overyde

Programmer

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor