Julian Parker
asked on
MySQL sum() case/if
Hi All,
This is a related question.
I need to calculate the cost to get a result similar to the following;
status chargeable nonchargeable cost
loan1 100 300 400
loan2 500 300 800
loan3 100 300 400
I've tried to use the code to calculate the cost of each but cant get the correct syntax, instead I just calculate the number of entries where the status is chargeable (that'll be the 1 else 0 bit) I have tried modifying the syntax to use a.cost but it just keeps giving an error.
Current code is below.
This is a related question.
I need to calculate the cost to get a result similar to the following;
status chargeable nonchargeable cost
loan1 100 300 400
loan2 500 300 800
loan3 100 300 400
I've tried to use the code to calculate the cost of each but cant get the correct syntax, instead I just calculate the number of entries where the status is chargeable (that'll be the 1 else 0 bit) I have tried modifying the syntax to use a.cost but it just keeps giving an error.
Current code is below.
select b.booking_type as status,
sum(case when a.chargeable = 'Yes' then 1 else 0 end) chargeable,
sum(case when a.chargeable = 'No' then 1 else 0 end) nonchargeable,
sum(a.cost) as cost
from BOOKING b, ADMIN a
where b.booking_id = a.booking_id
and a.approved = "Approved"
and a.delivery_date between '2010-07-01' and '2010-07-31'
group by booking_type
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was trying to use `then sum(a.cost)...` and wasnt getting anywhere...
I can now finish for the day happy :-)