Geting data for a chart


#1

I am kind of new to MySQL and databases and I am having trouble getting the right information out of a database for a chart I am using. I have a database with three tables. The tables look like this:

Salesmen Table:
Salesman ID Salesman name Experience status

Products Table:
Product ID Product name Product description Product Cost

Orders:
Salesman ID Product ID Quantity Date
Foreign keys are salesman id and product id for orders.

I want to display the total product cost for each salesman and separate it out by experience.
The output should look like this:

Very Experience 34
New 12
Experience 45

I am able to figure out how to get the Salesman Id with the total product cost. But I am unable to figure out how to get it to show only the experience and product cost. This information is going into an graphic chart. If someone can help me I would appreciate it.

Thanks,
r.

Here is my code in the PHP page that queries the dabase .
This codes does work but it only gives the salesman id and the total product cost.
[php]
@ $db = new mysqli(‘localhost’,‘root’,‘newdata’,‘sales’);
$query = "select sm.salesmanid, sum(or.quantity) ";
$query .= "from salesmen as sm, orders as or ";
$query .= "where sm.salesmanid = or.salesmanid ";
$query .= "group by or.salesmanid ";
$result = $db->query( $query );
$numrecs = $result->num_rows; //how many records in result set
for ($i = 0; $i < $numrecs; $i++ ) {
$row = $result->fetch_array(); //each record is array
$Dataset->addPoint($row[0], $row[1], substr($row[0],0,1));
}
[/php]


#2

Sounds like you just need to add 1 column to the select.

[php] $query = "select sm.salesmanid, sum(or.quantity), sm.experiencestatus ";
$query .= "from salesmen as sm, orders as or ";
$query .= "where sm.salesmanid = or.salesmanid ";
$query .= "group by or.salesmanid ";[/php]


#3

Topcoder:

Thank you for your reply. I added the other column but I still get the out put for salesman id instead of experience status. For a minute I though it would work but it is still the same as I had it before. If you think of anything else please let me know.

Thanks,
r.


#4

Can you post the output you get from it, so i can see it and make adjustments…


#5

TopCoder:

Here is the output:

Very experience
1
75
New
2
25
Experience
3
15
Experience
4
10
New
5
10
Very experience
6
35
New
7
8
Very experience
8
15

The experience is listed along with the salesman Id number with the total count. I need to show all the experience ids with the total number. Example if I have two new experience salesmen and both of there product cost was totaled to 50 (25 for each new experience salesman) then I need to have only the following printed out:
New experience 75

This would be for each experience. So I guess somehow I need the total of each salesman with the same experience and all of their totals. I hope I am explaining this clearly.

Thanks
r.


#6

Try This.

[php]$query = "select sum(or.quantity), sm.experiencestatus ";
$query .= "from salesmen as sm, orders as or ";
$query .= "where sm.salesmanid = or.salesmanid ";
$query .= "group by or.experiencestatus ";[/php]


#7

Topcoder:

Sorry it has taken awhile to get back to you. I got hung up on another database problem that I am trying to figure out where I update a drop down menu hooked up to a database. I think your code will work. When I get on it again I will let you know.
Thanks,
r.