title: SQL group by not working (from 3 queries)
body: <p>I have three queries, I already created another query in which I combined the three queries. 
  The queries are:</p>
  
  <pre><code>with tbl1 as(
      SELECT coachEID,role,coacheeEID, 
      IIF(count(status)&gt;IIF(a.role='SME',1,2),IIF(a.role='SME',1,2),count(status))
      AS total from coaching z
      JOIN tbl_calendar v
      ON z.starttime BETWEEN v.datestart and v.dateend
      JOIN roster a on z.coachEID=a.EID
      WHERE z.status='completed'
      AND v.month='1' and v.year='2017'
      AND a.location='manila'
  GROUP BY  coacheid, coacheeeid, status, role),
  
  tbl2 as(
      SELECT y.role, y.eid, x.eid as coachee, IIF(y.eid=x.sme,1,2) AS target
      FROM roster y
      JOIN roster x ON (x.supervisor = y.eid OR x.sme = y.eid OR x.teamlead = y.eid)  AND x.eid &lt;&gt; y.eid 
      AND x.employstatus = 'Active'
      WHERE y.employstatus = 'active' 
      AND y.Role IN ('SME', 'Junior Team Lead','Team Lead', 'Shift Lead', 'Operations Lead', 'Quality Lead', 'Policy Lead') 
      AND y.location = 'manila'),
  
  tbl3 as(
      SELECT x.coachEID,x.CoacheeEID,  sum (x.NoOfApproved) as exempt, x.month, x.year from exemptitems x
      LEFT JOIN roster y on y.eid=x.Coacheid
      WHERE x.month='1' and x.year='2017'
      AND y.eid=x.coacheid and x.NoOfApproved is not NULL
      GROUP BY x.CoachEID, x.CoacheeEID, x.Month, x.year)
  </code></pre>
  
  <p>Final query is:</p>
  
  <pre><code>select IIF(tbl2.role is NULL, tbl1.role,tbl2.role) as role, 
         IIF(tbl1.coachEID is NULL,tbl2.eid,tbl1.coachEID) as coacheid,
         IIF(tbl1.coacheeEID is NULL, tbl2.coachee,tbl1.coacheeEID) as coacheeeid, 
         IIF(total is NULL,0,total) as total,
         IIF(tbl2.target is NUll,total,tbl2.target) as ttarget,
         IIF(tbl3.exempt is NULL,0,tbl3.exempt) as texemption, 
         IIF(tbl2.target is NULL,total,tbl2.target)-IIF(tbl3.exempt is NULL,0,tbl3.exempt) as ttotal
  from tbl1
  FULL join tbl2  
    on tbl1.coachEID=tbl2.eid and tbl1.coacheeEID=tbl2.coachee
  left JOIN tbl3 
    on tbl1.coachEID=tbl3.CoachEID
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/kGp20.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/kGp20.png" alt="query result"></a></p>
  
  <p>My question is, what method should I use to have it in group? I already tried group by but it's not working. I wanted it to group by role, coacheid and their total coaching (total column). Whenever I try to use GROUP BY role,coacheid, coacheeeid on the end statement of the final query, all I get was "Ambiguous column name 'role'" and "Ambiguous column name 'coacheid'"</p>