laika222
4/18/2017 - 5:37 PM

FORMAT() places result in format with thousands comma separator, and with a specified number of decimal places (the number after the comma i

FORMAT() places result in format with thousands comma separator, and with a specified number of decimal places (the number after the comma in the arguments), in the following fashion: #,###,###.##. A set of results can be formatted by placing a select statment in for the first argument.

-- Takes number 1234 and returns it with two decimal places as 1,234.00
SELECT FORMAT(1234,2);

-- Takes number 1234.56422132436 and returns it with three decimal places as 1,234.564
SELECT FORMAT(1234.56422132436,3);

-- Shows a nested SELECT statment within FORMAT(). This takes the result set of the Price column of the products1 table and formats it in this case as having thousands decimal separators with one decimal place. Therefore, a result like 1325567.17 will be shown as 1,325,567.1, resulting in numbers that are more easily readable.
SELECT FORMAT(Price,1) FROM products1;

-- Shows a nested SELECT statment within FORMAT (the sixth and final argument in line 13). This takes the raw SUM(b.RBI) numbers, such as 1325, and formats them with a decimal separator with zero decimal places, resulting in 1,325, resulting in numbers that are more easily readable.
SELECT a.nameFirst, a.nameLast, a.birthState AS 'Birth State', b.RBI, a.playerID, FORMAT(SUM(b.RBI),0) AS 'Career RBIs'
FROM Master a
JOIN Batting b
ON a.playerID=b.playerID
GROUP BY a.playerID HAVING a.birthState = 'IL'
ORDER by SUM(b.RBI) ASC;