Add IF THEN clause in SELECT statement
Using MySQL 5 and Coldfusion8. I have fields for "price" and "price_status" among other fields. Price_status has 2 options, set and notset. The user will enter a price and select if that price is set or not.
My select query looks like this...
SELECT SUM(amount) as amount, ROUND(AVG(weight)) as avg_weight, ROUND(AVG(price),2) as avg_price
FROM deal_master LEFT JOIN (deal_lots LEFT JOIN sale_lots ON deal_lots.lot_id = sale_lots.origin_lot_id) ON deal_lots.deal_master_id = deal_master.deal_master_id
WHERE sale_lots.delivery_taken_d
1 of my inventory units includes fields for amount, weight and price. In this query, I want to include the amount and weight from all of my products but I only want to average the prices that are set. I can't add a where clause like WHERE price_type = set since that would then exclude all of my weight and amounts. Is there anyway I could tell it to use weights and amounts on all inventory, but only use prices where price_type = set?
My select query looks like this...
SELECT SUM(amount) as amount, ROUND(AVG(weight)) as avg_weight, ROUND(AVG(price),2) as avg_price
FROM deal_master LEFT JOIN (deal_lots LEFT JOIN sale_lots ON deal_lots.lot_id = sale_lots.origin_lot_id) ON deal_lots.deal_master_id = deal_master.deal_master_id
WHERE sale_lots.delivery_taken_d
1 of my inventory units includes fields for amount, weight and price. In this query, I want to include the amount and weight from all of my products but I only want to average the prices that are set. I can't add a where clause like WHERE price_type = set since that would then exclude all of my weight and amounts. Is there anyway I could tell it to use weights and amounts on all inventory, but only use prices where price_type = set?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
http://dev.mysql.com/doc/refman/5.0/en/case-statement.html