CODEWITHSUNDEEP
mysql
arbme
arbme

Reputation: 4931

How to use alias in SELECT

I have the mysql statement below all works fine until I add (sub_total - discount_total) AS total,.

I'm guessing this is because I am calling on aliases that are not defined yet (sub_total and discount_total).

How do I define the aliases so it doesn't throw an error #1054 - Unknown column 'sub_total' in 'field list'

SELECT SQL_CALC_FOUND_ROWS 
    `bookings`.`id`,
    `bookings`.`client_id`,
    `bookings`.`vehicle_id`,
    `bookings`.`vehicle_hire`, 
    (insurance_driver_1 + insurance_driver_2 + insurance_driver_3) AS insurance_total, 
    `bookings`.`bedding_qty`, 
    `bookings`.`bedding_price`, 
    `bookings`.`promo_discount`, 
    `bookings`.`promo_is_percent`, 
    `bookings`.`promo_code`, 
    (vehicle_hire + insurance_driver_1 + insurance_driver_2 + insurance_driver_3) + (bedding_qty * bedding_price) AS sub_total, 
    CASE 
        WHEN promo_is_percent = 1 
            THEN (((vehicle_hire + insurance_driver_1 + insurance_driver_2 + insurance_driver_3) + (bedding_qty * bedding_price)) / 100) * promo_discount 
        WHEN promo_is_percent = 0 
            THEN promo_discount 
    END as discount_total,
    (sub_total - discount_total) AS total   
FROM `bookings`
WHERE `bookings`.`status` = 'Quote'
ORDER BY  `bookings`.`id` desc
LIMIT 0, 10

Thanks

Update

Working example below,

`SELECT SQL_CALC_FOUND_ROWS *,     
(sub_total - discount_total) AS total
FROM (
SELECT  
`bookings`.`id`,
`bookings`.`client_id`,
`bookings`.`vehicle_id`,
`bookings`.`vehicle_hire`, 
(insurance_driver_1 + insurance_driver_2 + insurance_driver_3) AS insurance_total, 
`bookings`.`bedding_qty`, 
`bookings`.`bedding_price`, 
`bookings`.`promo_discount`, 
`bookings`.`promo_is_percent`, 
`bookings`.`promo_code`, 
(vehicle_hire + insurance_driver_1 + insurance_driver_2 + insurance_driver_3) +  (bedding_qty * bedding_price) AS sub_total,
CASE 
    WHEN promo_is_percent = 1 
        THEN (((vehicle_hire + insurance_driver_1 + insurance_driver_2 +    insurance_driver_3) + (bedding_qty * bedding_price)) / 100) * promo_discount 
    WHEN promo_is_percent = 0 
        THEN promo_discount 
END as discount_total
FROM `bookings`
WHERE `bookings`.`status` = 'Quote'
) AS src
ORDER BY  src.`id` desc
LIMIT 0, 10`

Upvotes: 0

Views: 131

Answers (1)

ermagana
ermagana

Reputation: 1230

For your question I would solve it like so:

SELECT *,     
(sub_total - discount_total) AS total
FROM (
SELECT SQL_CALC_FOUND_ROWS 
`bookings`.`id`,
`bookings`.`client_id`,
`bookings`.`vehicle_id`,
`bookings`.`vehicle_hire`, 
(insurance_driver_1 + insurance_driver_2 + insurance_driver_3) AS insurance_total, 
`bookings`.`bedding_qty`, 
`bookings`.`bedding_price`, 
`bookings`.`promo_discount`, 
`bookings`.`promo_is_percent`, 
`bookings`.`promo_code`, 
(vehicle_hire + insurance_driver_1 + insurance_driver_2 + insurance_driver_3) +  (bedding_qty * bedding_price) AS sub_total,
CASE 
    WHEN promo_is_percent = 1 
        THEN (((vehicle_hire + insurance_driver_1 + insurance_driver_2 +    insurance_driver_3) + (bedding_qty * bedding_price)) / 100) * promo_discount 
    WHEN promo_is_percent = 0 
        THEN promo_discount 
END as discount_total
FROM `bookings`
WHERE `bookings`.`status` = 'Quote'
) AS src
ORDER BY  src.`id` desc
LIMIT 0, 10

but this is only if you're definitely trying to avoid retyping the whole formula for your column.

have you also considered creating a calculated column in mysql?

Upvotes: 1

Related Questions