472,784 Members | 937 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,784 software developers and data experts.

Mysql ROUND() inside SUM()

I have a table1 as:
id(int); percent(double,2); a(double,2); b(double,2); c(double,2);.....z(double,2)
1; 0.12; 1.33; 1.26; 1.89;........
2; 0.11; 1.55; 1.22; 1.56;........
and many rows

i always need to sum up query as:
SELECT SUM(ROUND(a*percent,2)+ROUND(b*percent,2)+ROUND(c* percent,2)) AS sum_abc, SUM(ROUND(d*percent,2)+ROUND(e*percent,2)+ROUND(f* percent,2)) AS sum_def ...... FROM table1;

My problem is:
I found that doing many ROUND() inside SUM() in the query are slower, as it may sum up to few thousand rows. As seen, it Rounds in every single row.
So, i try to store a*percent value in new column per_a, for every a......z:
id(int); a(double,2); per_a(double,2); b(double,2); per_b(double,2);.....per_z(double,2)

So i can write my query as:
SELECT SUM(per_a+per_b+per_c) AS sum_abc, SUM(per_d+per_e+per_f) AS sum_def ...... FROM table1;

so that i dont need to round up. BUT now, my table columns become more and bigger in size, double size of the original.
May i know which one is suitable and better in performance?
Apr 21 '07 #1
0 2291

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5...
by: aa | last post by:
I use the following fragment of code to output datf from MySQL: ====================================================== $chan = mysql_connect ($db_host, $username, $password); mysql_select_db...
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
by: Bob Bryan | last post by:
I am using a select statement to obtain a result set back with aggregated data. The problem is that I am seeing column data with 11 to 13 digits after the decimal point. I tried using the STR...
by: smorrey | last post by:
Hello all, this might better be suited for the MySQL newsgroup, but I figured I'ld post here and see if anyone can help me. I'm trying to create a simple transaction handling system where users...
by: dhutton | last post by:
Hello - Question... What would be the best way to round a number off and change it to currency with the below statement? SELECT Cycle AS BillCycle, SUM(PrevBillAmt) - SUM(Payment) +...
by: ScottCase | last post by:
Hello, I have this sql statement and it works fine with mysql v4 $query = "SELECT id, name, jv_signups, mem_signups, ( SELECT SUM( jv_signups ) ) + ( SELECT SUM( mem_signups ) ) AS total...
by: inexion | last post by:
Hello, I'll try to explain what I'm trying to accomplish here, hopefully not too bad a task. Basically I have an existing table with data that will be operated on to form new records etc... ...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.