470,871 Members | 1,347 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,871 developers. It's quick & easy.

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 2173

Post your reply

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

Similar topics

19 posts views Thread by Westcoast Sheri | last post: by
reply views Thread by Philip Stoev | last post: by
reply views Thread by Mike Chirico | last post: by
4 posts views Thread by Bob Bryan | last post: by
10 posts views Thread by smorrey | last post: by
5 posts views Thread by inexion | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.