MySQL - 'seeing' other rows

Question

I have a query which produces a list of orders like this:

Invoice   Description       Qty    Amount    VAT       VAT code
3761      Flyers            -1     -100      0         Z
3761      Business Cards    -1     -75       -11.25    S
3761      Business Cards    -1     -75       -11.25    S
3761      Discount(10%)     -1     25        0         Z
3761      Delivery          -1     -27       -4.05     S

Essentially the VAT column is the Qty * Amount * 0.15, if the VAT Code is 'S' (I did this using an IF statement), otherwise 0.

This works fine for the orders apart from where there is a Discount row for that Invoice number (in this case Invoice 3761 has a Discount row). When there is a Discount row I need to work out the VAT differently (((Qty * (Amount - 10%)) * 0.15).

Is this even possible within MySQL to 'see' if there is a Discount row for the common Invoice number and change the VAT fields of other rows accordingly? If so what function should I use and can I combine this with an IF statement to produce the two VAT calculating formulas?

Answer 1

SELECT  i.*,
        Qty * Amount * 0.15 *
        COALESCE(
        (
        SELECT  1 - CAST(SUBSTRING(SUBSTRING_INDEX(description, '%)', 1), POSITION('(' IN description) + 1) AS DECIMAL) * 0.01
        FROM    invoice ii
        WHERE   ii.invoice = i.invoice
                AND ii.description RLIKE 'discount[^(]*\\([0-9]+\\%\\)'
        LIMIT 1
        ), 1) AS vat
FROM    invoice i

Answer 2

To add to my question- the amount of discount is not always 10%. It is stored in the Description string (usually 'Discount (5%)' or 'Online Discount (10%)'). In my SELECT I use this to change it to a numerical value field called "DiscountPercent":

IF(ol.Type = 'DISCOUNT', REPLACE(REPLACE(REPLACE(ol.Description, '%)', ''), 'Discount (', ''), 'Online ', ''), NULL) AS DiscountPercent,

Now lets take your line:

WHEN 1 THEN Qty * Amount * 0.9 * 0.15

And modify it to deduct a percentage rather than a number like 0.9:

WHEN 1 THEN (Qty * Amount * (1 - 1 / DiscountPercent)) * 0.15

What I need to know is how I can use this line to access the "DiscountPercent" field correctly. I get the error "Unknown column 'DiscountPercent' in 'field list'" as it is not an actual field in my table but a field in the results.