Problem with a MySQL JOIN Query

[Redgie]

Hi all, I'm trying to join three tables (Staff, Sales and SaleItems) to produce a list of Staff, how many Sales they made, how many Items they sold (Multiple items can be present in each sale), and then their profit from those sales.

My query currently is:

SELECT Staff.Forename, COUNT(Sales.SaleID) AS Sales, SUM(Quantity) AS Items, SUM(Price*Quantity) AS Revenue, SUM(Cost*Quantity) AS Cost
FROM SaleItems
LEFT JOIN Sales ON SaleItems.SaleID = Sales.SaleID
JOIN Staff ON Sales.StaffID = Staff.StaffID
WHERE Sales.Deleted = '0' AND Sales.Refunded = '0'
GROUP BY Sales.StaffID

This works, with the exception of counting the number of Sales made by each member of staff, where even though each member only currently has 1, it insists that the second member of staff has 2? Is there something in the query that is counting the wrong thing? Have I done something catastrophically wrong from the get go? This is about my 7th attempt going at it from different angles.

Ideally I'd like to avoid Sub-Queries, as if I was going to use them, a PHP loop may be just as appropriate given the context.

Thanks!

EDIT: For clarification:

Desired Output
Staff Member | Sales | Items | Revenue | Cost
Name1 | 1 | 2 | 10 | 4
Name2 | 1 | 6 | 1100 | 830
Name3 | 1 |1 |51 | 2

Current Output
Staff Member | Sales | Items | Revenue | Cost
Name1 | 1 | 2 | 10 | 4
Name2 | 2 | 6 | 1100 | 830
Name3 | 1 |1 |51 | 2

Further Edit
Here's a less complicated version I hope will better illustrate my issue.

SELECT StaffID, COUNT(Sales.SaleID) AS Sales
FROM Sales
JOIN SaleItems ON Sales.SaleID = SaleItems.SaleID
GROUP BY Sales.StaffID

This returns "2" for StaffID 1's Sales, though there is only one sale record for that member of staff.

 

[Redgie]

I've managed to get things working by using "DISTINCT" for the Sales.SaleID, in order to ensure the mystery sale isn't counted. What I believe the issue was is that it counts the returned rows, which include the Sale Items, why this brought it to 2 and not 3 (2 Sale Items + 1 Sale) I don't know, so would still appreciate any help anybody can give!