2

CODE SYNTAX

As you can see from the columns, i wanted the output to be 2 decimal place as shown on EndOfYearForecast. Any way around it?enter image description here

2

You can use the ROUND() function

SELECT
CONCAT("£",ROUND(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",ROUND(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",ROUND(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

You can also use the FORMAT() function if you want commas in the display

SELECT
CONCAT("£",FORMAT(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",FORMAT(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",FORMAT(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

EXAMPLE

mysql> SET @NUM=1234.56789; SELECT ROUND(@NUM,2) RoundNum,FORMAT(@NUM,2) FormatNum;
Query OK, 0 rows affected (0.00 sec)

+----------+-----------+
| RoundNum | FormatNum |
+----------+-----------+
|  1234.57 | 1,234.57  |
+----------+-----------+
1 row in set (0.00 sec)

mysql>
0

You can cast as decimal and mentions scale number same as number of decimal places you need in your output. Details about decimal data type here. Using CAST and CONVERT see details here.

Sample:

DECLARE @MonthlyAverage AS FLOAT=179686.787500

SELECT CAST (@MonthlyAverage AS Decimal (18,2))

Result 179686.79

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.