MYSQL, Create a new Query for a Column with Decimal (M,D) in the workbench

Question

At the moment I am working on VAT for each country where the Sale arrival country.

this VAT is working well and correct, however, I having an issue with 0.0000000 as I tried put coalesce with 0.00 but it keeps displayed with '3632.608266' rather than '3632.60' which I am looking for

Here are MySQL queries I wrote

SUM(COALESCE((TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)-(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)/(VAT_Standard_Rate + 1),0.00)) AS 'decmical',

Here is a full version

SELECT 
    SALE_ARRIVAL_COUNTRY,
    tbl_countries.Country,
    VAT_Standard_Rate,
    SUM(COALESCE((TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)-(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)/(VAT_Standard_Rate + 1),0.00)) AS 'decmical',
    SUM(COALESCE(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)+(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)/(VAT_Standard_Rate + 1)-(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)) AS 'NET',
    SUM(COALESCE(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)-(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)/(VAT_Standard_Rate + 1)) AS 'VAT',
    SUM(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL) AS 'Total',
    TRANSACTION_CURRENCY_CODE as 'Currency'
FROM
    tbl_vat
INNER JOIN tbl_countries
INNER JOIN tbl_vat_rate
ON amazon.tbl_vat.SALE_ARRIVAL_COUNTRY=tbl_countries.alpha_2 AND tbl_countries.Country=tbl_vat_rate.Country_of_Purchase
GROUP BY tbl_countries.Country

Here is a screenshot of result with yellow highlight

S.O - why not to use double or float for currency ... https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency — Mawg says reinstate Monica, Feb 20 '18 at 10:26

score 1 · Accepted Answer · answered Feb 20 '18 at 10:26

1

You can use format function from SQL for that like this:

FORMAT(SUM(COALESCE((TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)-(TOTAL_ACTIVITY_VALUE_AMT_VAT_INCL)/(VAT_Standard_Rate + 1),0.00)), 2) AS 'decmical',

answered Feb 20 '18 at 10:26

MatejG

1,393
1
17
26

score 1 · Answer 2 · answered Feb 20 '18 at 10:27

You can use Round() function

ROUND(N,[D])
ROUND() rounds a number specified as an argument up 
to a number specified as another argument.

like this:

mysql> SELECT ROUND(-4.535,2);
+-----------------+
| ROUND(-4.535,2) |
+-----------------+
|           -4.54 | 
+-----------------+
1 row in set (0.00 sec)

OR

You can use Truncate function

TRUNCATE(X,D)
Returns the number X, truncated to D decimal places

like this:

mysql> SELECT TRUNCATE(1.223,1);
    -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
    -> 1.9

I like this function, i will notes this for this query function, thanks — Steven Smith, Feb 20 '18 at 11:44

MYSQL, Create a new Query for a Column with Decimal (M,D) in the workbench

2 Answers2