reaper reaper - 4 years ago 122
SQL Question

SQL group by not working (from 3 queries)

I have three queries, I already created another query in which I combined the three queries.
The queries are:

with tbl1 as(
SELECT coachEID,role,coacheeEID,
IIF(count(status)>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 <> 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)


Final query is:

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


query result

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'"

Answer Source

'Ambiguous column name' means, you forgot to specify table name in front of the column. Choose one and put it there (GROUP BY).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download