title: MySQL - Calculating Net Dues against two tables
body: <p>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.  </p>
  
  <pre><code>Table:Doctors 
  idDoctors
  PracticeID
  FirstName
  LastName
  
  Table: Vendor Sales
  Id
  ProductSales
  SalesCommission
  DoctorFirstName
  DoctorLastName
  </code></pre>
  
  <p>Here is the Query I am struggling with:</p>
  
  <pre><code>SELECT t1.PracticeID
  , SUM( t2.ProductSales ) 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
  </code></pre>
  
  <p>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.</p>
  
  <p>Result (limited to one result for this example)</p>
  
  <pre><code>PracticeID  Total_Sales     Doctor_Count    Dues    Credit
  Practice A  16583.04               4    600     304.07360
  </code></pre>
  
  <p>This is what the result should be:</p>
  
  <pre><code>PracticeID  Total_Sales     Doctor_Count    Dues    Credit
  Practice A  16583.04               3    450 304.07360
  </code></pre>
  
  <p>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.<br>
  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.</p>