CODEWITHSUNDEEP
mysqldatabaseaggregate-functions
Brent
Brent

Reputation: 171

SQL - Count Total Number of Orders By Salesperson

I'm a computer science student working on a homework assignment. I am attempting to total the number of orders for each salesperson. I want my final output to display the salesperson's name along with the number of orders he/she is given credit for.

The information is stored in two separate tables.

Sample data.

Table 1 - order_t

OrderID CustomerID  OrderDate   FulfillmentDate SalespersonID   ShipAdrsID
1   4   2014-09-08  2014-11-25  3   NULL
2   3   2014-10-04  0000-00-00  3   NULL
3   1   2014-07-19  0000-00-00  2   NULL
4   6   2014-11-01  0000-00-00  5   NULL
5   4   2014-07-28  0000-00-00  3   NULL
6   4   2014-08-27  0000-00-00  3   NULL
7   1   2014-09-16  0000-00-00  2   NULL
8   4   2014-09-16  0000-00-00  3   NULL
9   6   2014-09-16  0000-00-00  5   NULL

Table 2 - salesperson_t

SalespersonID   SalespersonName 
1   "Doug Henny"    
2   "Robert Lewis"  
3   "William Strong"    
4   "Julie Dawson"

Here is my latest query. The problem is it only returns data for a single salesperson that has the ID of 2. I have no idea why this is happening.

SELECT SalespersonName, COUNT(DISTINCT order_t.SalespersonID) AS TotalOrderQuantity
FROM salesperson_t, order_t
WHERE salesperson_t.SalespersonID = order_t.SalespersonID;

Upvotes: 1

Views: 11338

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

I suggest the use of inner join and count(*) with group by 

SELECT SalespersonName, COUNT(*)  AS NumberOfOrder
FROM salesperson_t 
INNER JOIN order_t on (ordert_t.SalespersonID = salesperson_t.SalespersonID )
GROUP BY order_t.SalespersonID
ORDER BY order_t.SalespersonID

Upvotes: 1

Vlad Călin Buzea
Vlad Călin Buzea

Reputation: 559

You need to use a group by clause

SELECT SalespersonName, COUNT(order_t.SalespersonID) AS TotalOrderQuantity
FROM salesperson_t join order_t on (ordert_t.SalespersonID = salesperson_t.SalespersonID )
group by order_t.SalespersonID

Upvotes: 2

Related Questions