Help with SUM

[Canman2005]

Hi all

 

I have the following table

 

ID  UID  COST  VAT

1    11    2.99    17.5

2    11    1.99    17.5

3    11    0.50   

4    32    9.99    17.5

5    23    2.40

 

ID = Unique ID

UID = User ID

COST = Price (excluding VAT)

VAT = VAT Percentage %

 

What I want to do is grab all UID's with number 11, so a simple QUERY for this  would return;

 

ID  UID  COST  VAT

1    11    2.99    17.5

2    11    1.99    17.5

3    11    0.50   

 

But what I want to then do is add up all the costs, so that would equal

 

2.99 + 1.99 + 0.50 = 5.48

 

But I also want to include the VAT % if one exists, so with the above it would be

 

2.99 + VAT% = 3.51

 

1.99 + VAT% = 2.34

 

0.50 + VAT% = 0.50 (your notice 0.50 doesnt have a VAT value)

 

So it would take those values and add them together, giving

 

3.51 + 2.34 + 0.50 = 6.35

 

Can this be done or will it require tons of QUERIES and adding values?

[Adam]

Do it in your PHP, not within the query. So when you loop through the rows, keep a variable, say $total and add to that with each loop. Something like:

 

while ($row = mysql_fetch_assoc($query)) {
    // print out your table and stuff ..
    $total = $total + ($row['cost'] + ($row['cost'] / $row['vat']));
}

echo 'TOTAL: ' . $total;

[Canman2005]

I seem to get a

 

Warning: Division by zero

 

error when doing that, any ideas?

[Adam]

Ahh yeah replace the $total calculation line with:

 

$total = ($row['vat'] != null) ? $total + ($row['cost'] + ($row['cost'] / $row['vat'])) : $total + $row['cost'];

 

Not tested but should work!

 

Adam

[Adam]

I've just editted it cause i made a mistake so make sure you use latest one..

[Canman2005]

Thanks MrAdam, but still getting

 

Warning: Division by zero

 

any ideas?

[Adam]

???

 

if ($total == '' || $total == null || $total == 0) {
    $total = $total + $row['cost'];
} else {
    $total = $total + ($row['cost'] + ($row['cost'] / $row['vat']));
}

 

try that?

[Canman2005]

Nope it still produces that error, it seems to be with

 

($row['net_retail_price_exc_vat'] / $row['vat_rate'])

 

if you change that to

 

($row['net_retail_price_exc_vat'] / 2)

 

for example, it works fine

 

any other ideas?

[Adam]

What data type is set for the vat field in your database?

[Canman2005]

It's just set to 'text' only because it was an old database with lots of data and I didnt want to alter any data by changing the data type

[Adam]

ahh okay, it might be trying to divide by a string - try converting it to a float (not 100% sure this would work):

 

if ($total == '' || $total == null || $total == 0) {
    $total = $total + $row['cost'];
} else {
    $vat = (float) $row['float'];
    $total = $total + ($row['cost'] + ($row['cost'] / $vat));
}

 

Oh also make sure there's no % on the front of it either!

[Barand]

SELECT uid, SUM(COST) as totcost, SUM(cost * (100+vat)/100) as totgross
FROM tablename
WHERE uid = 11
GROUP BY uid

[fenway]

That's right... no need to ugly php code here.