is it possible to do what I have attempted in the SQL attached? I think the example speaks for itself, if there is only one item in an order the commission should be (item_count * 1.00), but if it's greater than one, the commission fee should be (item_count * 0.75).
Can I place 'if' in a sql query like this?
select A.orders_id, count(B.orders_id) As ItemCount,
if ItemCount = 1 then (count(B.orders_id) * 1.00) else (count(B.orders_id) * .75) AS CommissionFee, d.manufacturers_id, d.manufacturers_name
from orders A, orders_products B, products C, manufacturers D
where date_purchased > '01/01/2008'
and a.orders_status in (4,8)
and a.orders_id = b.orders_id
and b.products_id = c.products_id
and c.manufacturers_id = d.manufacturers_id
group by a.orders_id, d.manufacturers_id, d.manufacturers_name
order by a.orders_id DESC