title: Finding difference of two columns with where clause - MySQL
body: <p>I'm trying to get the difference of two columns(<strong>OTHER</strong> AND <strong>SENIOR</strong>) with WHERE clause along with the Sum of their amount. The formulas are working fine, however, I'm not getting the result I want.  </p>
  
  <p>I tried to do it using <b>INNER JOIN</b> and unfortunately its not the result that I expected </p>
  
  <p>Here's my query: </p>
  
  <pre><code>select a.ID as EMPLOYEE, 
  sum(a.amount) as other,
  sum(b.amount) as senior, 
  (sum(a.amount) - sum(b.amount)) as result 
  from gndsale a 
  INNER join gndsale 
  b ON a.ID= b.ID 
  where a.TYPE = "5" and b.seniortype = "10" 
  group by a.ID
  </code></pre>
  
  <p>Result: </p>
  
  <p><a href="https://i.stack.imgur.com/1iNZY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1iNZY.png" alt="enter image description here"></a></p>
  
  <p>What I want to do is get the difference of <strong>Column (Other) where type = 5 and Column (Senior)  where seniortype = 10</strong></p>
  
  <p>Here is my query for Senior:</p>
  
  <pre><code> select ID as EMPLOYEE, sum(amount) 
   from gndsale 
   where seniortype = "10" 
   group by ID
  </code></pre>
  
  <p>Result: </p>
  
  <p><a href="https://i.stack.imgur.com/d7wrE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/d7wrE.png" alt="enter image description here"></a></p>
  
  <p>Here is my query for Other:</p>
  
  <pre><code>select ID as employee, sum(amount) 
  from gndsale 
  where type = "5" 
  group by ID
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/BHO4d.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BHO4d.png" alt="enter image description here"></a></p>
  
  <p>The result should be </p>
  
  <p><strong>9907 = 530 <br />
  9912 = 63.71</strong></p>
  
  <p>Anyone can help me with this? :( </p>
  
  <p>Sample/Expected output:</p>
  
  <p><a href="https://i.stack.imgur.com/sowXE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sowXE.png" alt="enter image description here"></a></p>