title: SQL Server 2005 One query to calculation two table
body: <p>I'm beginner in SQL Server </p>
  
  <p>I have three tables in Database hospital</p>
  
  <ol>
  <li>PatientFiles</li>
  <li>OtherServices </li>
  <li>PatientDeposit</li>
  </ol>
  
  <p>Two queries to display my result</p>
  
  <ul>
  <li><p>Query Number One.  Display PatientFilesID, TotalOtherServices</p>
  
  <pre><code>SELECT pf.ID AS PatientFileID, SUM(os.Quantum * os.Price) AS TotalOtherServices
  FROM PatientsFiles pf INNER JOIN OtherServices os ON pf.ID = os.Patient_File_ID 
  WHERE pf.ID = '14'
  GROUP BY pf.ID
  </code></pre></li>
  </ul>
  
  <p>It is true result </p>
  
  <pre><code>    PatientFileID  | TotalOtherServices
          14                194.00
  </code></pre>
  
  <ul>
  <li><p>Query Number Two.  Display PatientFilesID, TotalPatientDeposit</p>
  
  <pre><code>SELECT pd.Patient_File_ID AS PatientFileID, SUM(pd.Deposit) AS TotalPatientDeposit
  FROM PatientsDeposits pd
  WHERE pd.Patient_File_ID = '14'
  GROUP BY pd.Patient_File_ID
  </code></pre></li>
  </ul>
  
  <p>It is true result </p>
  
  <pre><code>    PatientFileID | TotalPatientDeposit
          14               450.00
  </code></pre>
  
  <ul>
  <li><p>My very tired to mix two queries</p>
  
  <pre><code>SELECT pf.ID AS PatientFileID, SUM(os.Quantum * os.Price) AS TotalOtherServices, 
  SUM(pd.Deposit) AS TotalPatientDeposit
  FROM PatientsFiles pf
  INNER JOIN OtherServices os ON pf.ID = os.Patient_File_ID  
  INNER JOIN PatientsDeposits pd ON pf.ID = pd.Patient_File_ID
  WHERE pf.ID = '14'
  GROUP BY pf.ID
  </code></pre></li>
  </ul>
  
  <p>It is false result</p>
  
  <pre><code>    PatientFileID  | TotalOtherServices | TotalPatientDeposit
           14                  582.00         1350.00
  </code></pre>
  
  <p>Thank you for help me in advance</p>