I added some custom columns:

Tax1, Tax2, Tax3, Tax4, Tax5, Average Tax, Price1, Price 2, TotalPrice

I need to perform with basic arithmetric calculation to fetch the Average Tax Column from the 5 other tax defined tax columns! Then use the Average Tax to calcuate with some other price columns.

Examples of calculation i want, I just don't know SQL to work it out in LSQL:

$Average_Tax = int(($Tax1 + $Tax2 + $Tax3 + $Tax4, $Tax5)/5);

$Average_Tax = sprintf ("%.2f", $Average_Tax);

$subaverage_tax = int(10 * $Average_Tax);

$subprice1 = int(20 * $Price1);

$subprice2 = int(20 * $Price2);

$TotalPrice = int(($subaverage_tax + $price1 + $price2)/1.87);

$TotalPrice = sprintf ("%.2f", $TotalPrice);

Much appreciated if anyone can define above into SQL!

I have a few cookies to give out to helpers! Actually three chocalate chip cookies:)

Any help?

It has only been 2 hours since your first question

Click the help link in the admin panel and have a read through the docs for GT::SQL::Table and GT::SQL::Condition

You could probably piece something together using the docs and existing code samples in the resources area and in posts in the customization forum.

Yeah, I've been waiting 5 weeks for an answer to one of mine...

You might also want to check out the Mathematical Functions section of the MySQL docs:

http://www.mysql.com/...nctions.html#IDX1230

Dan

Hehe, been reading these docs for half a day now, I think It'll take me another month to comprehend SQL and hopefully get it done! But thanks all for the help;) Yeah Paul your right, it was only 2 hours after my first reply, and I was too anxious!!!

Here's something that may help ya. Assuming you're at the point where you know how to get to the point of feeding mysql a query. $TBL->do_query( "... your query... "); Something that may help you greatly will be an UPDATE query. With it, you can set values on columns on a specific number of records or even do complex mathematical operations on your entire database.

So without furthur ado.


Because we haven't put any constraints on that query, it will affect the entire table set. Does that help

Something that may help you learn SQL code is to use mysqlman or espeically the newest myphpadmin (sinnce it handles functions) and use the query wizards to generate queries andsee what you can get. Even MS Access can show you the low-level sql from their wizards so it makes a good learning tool.

Just a caveat here though, certain functions may not be portable across different sql systems. If it works in mysql, it might not in postgres or msql, etc. Nothing to be concerned about if you're not going to distribute your code

Hi Aki,

Thank you so much I must admit that sure helps alot. I finally learned to do the mathematics after a day. Anyway where would be the best place to insert my codes? Maybe under a certain subroutine in Links::Build? or create a new sub for it?