MySQL - Calculating Net Dues against two tables

Thanks in advance for any assistance. I am new to SQL and have looked at several related threads on this site, and numerous other sites on Google, but have not been able to figure out what I am doing wrong. I have looked at sub-selects, various JOIN options, and keeping bumping into the wrong solution/result. I have two tables that I am trying to do a query on.

Table:Doctors 
idDoctors
PracticeID
FirstName
LastName

Table: Vendor Sales
Id
ProductSales
SalesCommission
DoctorFirstName
DoctorLastName

Here is the Query I am struggling with:

SELECT t1.PracticeID
, SUM( t2.P开发者_JAVA百科roductSales ) AS Total_Sales
, COUNT( t1.LastName ) AS Doctor_Count
, COUNT( t1.LastName ) *150 AS Dues
, SUM( t2.ProductSales * t2.SalesCommission ) AS Credit
FROM Doctors AS t1
JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
GROUP BY t1.PracticeID
LIMIT 0 , 30

The objective of the Query is to calculate net dues owed by a Practice. I am not yet attempting to calculate the net amount, just trying to get the initial calculations correct.

Result (limited to one result for this example)

PracticeID  Total_Sales     Doctor_Count    Dues    Credit
Practice A  16583.04               4    600     304.07360

This is what the result should be:

PracticeID  Total_Sales     Doctor_Count    Dues    Credit
Practice A  16583.04               3    450 304.07360

The problem is that Total Sales sums the aggregate sales transactions (in this case 4 sales entries totaling 16584.04). Each of the 4 sales has an associated commission rate. The Credit amount is the total (sum) of the commission.

The sales and credit numbers are accurate. But the Doctor count should be 3 (number of Doctors in the practice). Dues should be $450 (150x3). But as you can see it is multiplying by 4 instead of 3. What do I need to change in the query to get the proper calculations (Doctors and dues multiplied by 3 instead of 4? Or should I be doing this differently? Thanks again.

There are various odd things about your schema, and you have not provided the sample data to justify your asserted values.

The first oddity is that you have both first and last name for the doctor in the Doctors table, and in the Vendor Sales table - yet you join only on the last name. Next, you have an ID column, it seems, in the Doctors table, yet you do not use that in the Vendor Sales table for the joining column.

It is not clear whether there is one entry in the Vendor Sales table per doctor, or whether there can be several. Given the counting issues you describe, we must assume there can be several entries per doctor in the Vendor Sales table. It also isn't clear where the vendor is identified, but we have to assume that is not germane to the problem.

So, one set of data you need is the number of doctors per practice, and the dues (which is 150 currency units per doctor). Let's deal with that first:

SELECT PracticeID, COUNT(*) AS NumDoctors, COUNT(*) * 150 AS Dues
  FROM Doctors
 GROUP BY PracticeID

Then we need the total sales per practice, and the credit too:

SELECT t1.PracticeID, SUM(t2.ProductSales) AS Total_Sales,
       SUM(t2.ProductSales * t2.SalesCommission) AS Credit
  FROM Doctors AS t1
  JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
 GROUP BY t1.PracticeID

These two partial answers need to be joined on the PracticeID to produce your final result:

SELECT r1.PracticeID, r1.NumDoctors, r1.Dues,
       r2.Total_Sales, r2. Credit
  FROM (SELECT PracticeID, COUNT(*) AS NumDoctors, COUNT(*) * 150 AS Dues
          FROM Doctors
         GROUP BY PracticeID) AS r1
  JOIN (SELECT t1.PracticeID, SUM(t2.ProductSales) AS Total_Sales,
               SUM(t2.ProductSales * t2.SalesCommission) AS Credit
          FROM Doctors AS t1
          JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
         GROUP BY t1.PracticeID) AS r2
    ON r1.PracticeID = r2.PracticeID;

That should get you the result you seek, I believe. But it is untested SQL - not least because you didn't give us appropriate sample data to work with.