CODEWITHSUNDEEP
mysqlsql
Edvinas Linkevičius
Edvinas Linkevičius

Reputation: 23

Mysql query - round() function

i want to ask, does exist some way to use selected value from leftjoin table, into round function as decimal parameter.

For example.: ROUND(sum(stats_bets_hourly.turnover_sum / currencies.rate ), 2) AS turnover_sum

Should be: ROUND(sum(stats_bets_hourly.turnover_sum / currencies.rate ), currencies.comma) AS turnover_sum

Thanks and sorry for my english.

UPDATE: Sorry that was badly formulated question. Round is working fine, but if currencies.comma value is 0 then query response is - 75312.000000, if currencies.comma value is 2, then - 75312.480000, if instead of currencies.comma i just writing 0 or 2 then i got - 75312 and 75312.48.

Upvotes: 1

Views: 527

Answers (3)

Edvinas Linkevičius
Edvinas Linkevičius

Reputation: 23

Sorry that was badly formulated question. Round is working fine, but if currencies.comma value is 0 then query response is - 75312.000000, if currencies.comma value is 2, then - 75312.480000, if instead of currencies.comma i just writing 0 or 2 then i got - 75312 and 75312.48.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Yes, of course you can use a column instead of the numeric literal in ROUND.

That's easy to demonstrate:

select round(123.4567, pos) from (select 2 as pos) x;

In your case which is

ROUND(sum(stats_bets_hourly.turnover_sum / currencies.rate ), currencies.comma)

there must be just one currencies.comma value you are dealing with in your query (by having currencies.comma or currencies.id in your GROUP BY clause, or by limiting them in the WHERE clause.) If you are dealing with multiple currencies.comma values, then you probably need two steps, e.g.:

select
  sum(turnover_partsum) as turnover_sum
from
(
  select 
    c.comma,
    round(sum(sbh.turnover_sum / c.rate ), c.comma) AS turnover_partsum
  from currencies c
  join stats_bets_hourly sbh on ...
  group by c.comma
);

EDIT: Just one more thought on that: currencies.comma tells you how to round when doing calculations? That doesn't seem likely. Maybe you'd rather want to display a currency with the according number of decimal places. That would be FORMAT rather than ROUND:

format(sum(sbh.turnover_sum / c.rate ), c.comma)

Upvotes: 1

A J
A J

Reputation: 4024

Yes, you can do so as long as it is an integer number.

Upvotes: 1

Related Questions