[MS SQL] UNION ALL Help

[shifty]

Design a Union All query to compute salary bonuses using the following guide


Teacher class >= 20 AND >= 90% Enrollment = $100 Bonus
Teacher class >= 20 AND < 90% Enrollment = $50 Bonus
Teacher class < 20 AND >= 90% Enrollment = $25 Bonus
Teacher class < 20 AND < 90% Enrollment = $1 Bonus
I wanted to use an IIF but my Prof hopes make an "efficient" UNION ALL.

It's all in one table with variables: Class.Teacher, Class.Enrollment, Class.Capacity

Can anyone point me out where to start? I made a working IFF table but I can't figure out how to make the bonuses work without 4 union all's... This is what I have so far but the IIF isn't working in this form.


SELECT Class.Teacher,
SUM(((Class.Enrollment / Class.Capacity >= 0.90) AND IFF(Class.Enrollment >=20, "$100.00", "$25.00")))) AS TB
FROM Class
GROUP BY Class.Teacher

[June7]

Really don't know why a UNION ALL is needed.

Don't put numbers within quote marks.

SELECT Class.*, Enrollment/Capacity AS PctEnroll, IIf(PctEnroll >= 0.90, IIf(Enrollment >= 20, 100, 25), IIf(Enrollment >= 20, 50, 1)) AS Bonus FROM Class;


Sum() is an aggregate function and is used to calc summary data of records in a TOTALS (GROUP BY) query - in other words, add up all the data in a field or expression for ALL records. If that's what you want:

SELECT Sum(IIf(Enrollment/Capacity >= 0.90, IIf(Enrollment >= 20, 100, 25), IIf(Enrollment >= 20, 50, 1))) AS SumBonus FROM Class;

[shifty]

That query works beautifully. Ill just ignore the Union then Thank you!