Reports sometimes include values that need to be rounded. You can do the rounding in your program or on the database server, but you need to be consistent.

Suppose you are required to produce a bill such as that shown in Table 5-11. You must add a 4 percent surcharge to every item. For each of the three items, the `Surcharge` column shows 4 percent of the price. In the `Total` row you have calculated the sum of the surcharges, not 4 percent of the total price.

Item | Price | 4% Surcharge |
---|---|---|

Awl | 100.10 | 4.00 |

Bowl | 100.10 | 4.00 |

Cowl | 800.80 | 32.03 |

Total | 1,001.00 | 40.03 |

There is a discrepancy because a 4 percent charge on $1,001.00 is $40.04. But the bill shows the sum of the `4%` `Surcharge` column as $40.03.

The other way to calculate these values is to apply the 4 percent to every column including the `Total`, as shown in Table 5-12.

Item | Price | 4% Surcharge |
---|---|---|

Awl | 100.10 | 4.00 |

Bowl | 100.10 | 4.00 |

Cowl | 800.80 | 32.03 |

Total | 1,001.00 | 40.04 |

You can have either the percentage calculation correct or the sum calculation correct. You can't have both be correct. When presenting data like this, the discrepancy in the sum is easier to spot by inspection than the discrepancy in the percentage. For this reason, it is common to sum the surcharges rather than calculate the surcharge on the sum. But you can do either.

Here is the calculation for each item:

mysql>SELECT item->,ROUND(net,2) AS Net->,ROUND(net*0.04,2) AS Charge->FROM line;+------+--------+--------+ | item | Net | Charge | +------+--------+--------+ | Awl | 100.10 | 4.00 | | Bowl | 100.10 | 4.00 | | Cowl | 800.80 | 32.03 | +------+--------+--------+

Here are the two ways to calculate the total surcharge.

`ROUND`the`SUM`:-
mysql>
**SELECT ROUND(SUM(net),2) AS Net**->**,ROUND(SUM(net*0.04),2) AS Charge**->**FROM line;**+---------+--------+ | Net | Charge | +---------+--------+ | 1001.00 | 40.04 | +---------+--------+ `SUM`the`ROUND`values:-
mysql>
**SELECT SUM(ROUND(net,2)) AS Net**->**,SUM(ROUND(net*0.04,2)) AS Charge**->**FROM line;**+---------+--------+ | Net | Charge | +---------+--------+ | 1001.00 | 40.03 | +---------+--------+

SQL Hacks

ISBN: 0596527993

EAN: 2147483647

EAN: 2147483647

Year: 2004

Pages: 147

Pages: 147

Authors: Andrew Cumming, Gordon Russell

Simiral book on Amazon

Flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net