Avatar of jammy-d0dger
jammy-d0dger asked on

IF statement inside SQL columns list

Hi experts,
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

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
jammy-d0dger

8/22/2022 - Mon
chapmandew

this should do it:

select A.orders_id, count(B.orders_id) As ItemCount,
case when count(B.orders_id) = 1 then count(B.orders_id) * 1.00 else (count(B.orders_id) * .75) end 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

Kevin Cross

You would use Case statement for the IF structure.  In SQL you cannot use column alias in the same query, so would have to repeat the formula or use a derived table.
select *, case ItemCount when 1 then ItemCount * 1.00 else ItemCount * 0.75 end AS CommissionFee
from (select A.orders_id, count(B.orders_id) As ItemCount,  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
) t
order by orders_id DESC

Open in new window

ASKER
jammy-d0dger

chapmandrew, thanks very much... works perfectly.  If I wanted to alter this query slightly so that I only got one row per manufacturer_id, with grand total of commission fees for each one, how would I do that?

Help much appreciated.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
chapmandew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kevin Cross

I just type way too slow for you Tim. :)
chapmandew

:)
ASKER
jammy-d0dger

HaHa, sorry mwvisa1, you were pipped to the post both times. chapmandrew, (or Tim), thank you very much... my query is working a treat and that's today's problem solved.  Guys, thanks very much for your submissions, I'll give mwvisa1 50 points as I can see you both said roughly the same thing, but it was Tim's two responses that I used so he gets the lion's share.  Need to dig out that old copy of Mavis Beacon Teaches Typing!  :) LoL
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.