Piduna Piduna -4 years ago 58
MySQL Question

How to round sql query to lower integer

For example i have a query:

select (select size from acc where acc_id = 1)/(select count(m_id) from m_acc JOIN dns ON dns.dns_id = m_acc.dns_id where acc_id = 1)*1000000;


the result of query:

3333333333.3330


i want to get

3333333333


i not need round to upper integer. i want round to lower integer. i tried do this:

select floor (select size from acc where acc_id = 1)/(select count(m_id) from m_acc JOIN dns ON dns.dns_id = m_acc.dns_id where acc_id = 1)*1000000;


but in a result i received this:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'size from acc where acc_id = 1)/(select coun' at line 1


Tell me please, where i have an error ?

Answer Source

Use FLOOR(). Try this:

select FLOOR
(
    (select size from acc where acc_id = 1)
    /
    (select count(m_id) from m_acc  JOIN dns ON dns.dns_id = m_acc.dns_id where acc_id = 1)
    *
    1000000
);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download