MYSQL: SELECT concat('$ ', SUM(`sale_price`)) gives a random value

In MYSQL:

The SELECT statement ...

SELECT concat('$ ', SUM(`sale_price`) ) FROM `temp_table`

... gives a random output value, while ...

SELECT concat('$ ', FORMAT(SUM(`sale_price`), 2)) FROM `temp_table`

... gives the correct output.

Why does this happen? What's wrong with the first statement?

Here's the SQL Code of the table on which I tried these statements.

SQL Code:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 01, 2016 at 11:04 AM
-- Server version: 5.6.21
-- PHP Version: 5.6.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `book_shop`
--

-- --------------------------------------------------------

--
-- Table structure for table `temp_table`
--

CREATE TABLE IF NOT EXISTS `temp_table` (
`sale_price` decimal(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `temp_table`
--

INSERT INTO `temp_table` (`sale_price`) VALUES
('21.20'),
('46.80'),
('23.50'),
('54.70');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Solution

SELECT concat('$ ', SUM(`sale_price`) ) FROM `transactions`

... and ...

SELECT concat('$ ', FORMAT(SUM(sale_price), 2)) FROM `transactions`

... do the exact same thing, only FORMAT(... , 2) ensures that your sales price is formatted as a decimal with two digits behind the . (which is the format typically used for monetary values in transactions between humans).

Without the FORMAT(... , 2), the format of your sales price depends on the format of your sales price in the database.

Note 1

When I try ...

SELECT concat('$ ', SUM(sale_price)) FROM test

... on a test table that contains the values 21.20, 46.80, 23.50 & 54.70 in the column sale_price, I get the following output :

$ 146.20000076293945 if I use FLOAT
$ 146.20 if I use something like DECIMAL(10,2)

These are the expected results in both cases. As you can see, FLOAT is less precise and should be avoided for that reason.

Note 2

Based on the information provided in the comments below, it appears that there's something wrong with your installation of PHPMyAdmin. Please uninstall and reinstall it (preferably a different version).