sql question regarding sum() and tax (MySQL 5.0.89)

Question

Select 
id,
sum(amount), 
vat 
From transactions WHERE id=1; 

Each record in this table has a vat percentage, I need to get the total amount in all records, however each amount has to be multiplied by its vat %.

Is there away to do this without looping through all records?

Answer 1

No.

SELECT id, SUM(amount*(1+vat)) AS total, vat
FROM transactions
WHERE id=1

Answer 2

You about something like

Select    id,
          sum(amount + amount*vat)       
From      transactions 
WHERE    id=1; 

This will get you the sum of all amounts plus the vat amounts.

Answer 3

You could do:

Select id, sum(amount*vat)
From transactions 
WHERE id=1; 

Answer 4

There are not returning the right amounts

SUM( amount * vat ) returns 1849.14
SUM(amount*(1+vat)) returns 2010.42
SUM( amount + amount * vat ) returns 2010.42

SELECT amount, vat FROM transactions WHERE id = 1;

/* 13 rows returned

        *amount*    * vat % *
        100.00 |    17.50
        25.28  |    2.28
        5.00   |    1.15
        5.00   |    1.15
        5.00   |    1.15
        1.00   |    1.18
        4.00   |    1.18
        3.00   |    1.18
        3.00   |    1.18
        1.00   |    1.10
        4.00   |    1.15
        4.00   |    1.10
        1.00   |    1.18
    */

Answer 5

you don't multiply by a percentage, 100*17.5 is 1750. 100*17.5/100 is the correct way or your percentage should be expressed as .175