How to transform a numeric value into CURRENCY FORMAT (Brazilian Real) in SELECT MYSQL

Question

Hello, Success and peace to all!

CREATE TABLE IF NOT EXISTS `lancamentos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` date DEFAULT NULL,
  `mes_referencia` varchar(50) DEFAULT NULL,
  `cliente` varchar(255) DEFAULT NULL,
  `valor` decimal(32,2) DEFAULT NULL,
  `descricao_lancamento` text, 
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=17253 DEFAULT CHARSET=latin1;

INSERT INTO `lancamentos` (`id`, `data`, `mes_referencia`, `cliente`, `valor`, `descricao_lancamento`) VALUES
(1, '2021-01-07', 'Janeiro', 'João', '350.40', 'Entrada do João'),
(2, '2020-01-08', 'Janeiro', 'José', '760.30', 'Entrada do José');

CREATE TABLE IF NOT EXISTS `pagamentos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` date DEFAULT NULL,
  `mes_referencia` varchar(255) DEFAULT NULL,
  `valor` decimal(10,2) DEFAULT NULL,
  `conta` varchar(100) DEFAULT NULL,
  `descricao` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12758 DEFAULT CHARSET=latin1;

INSERT INTO `pagamentos` (`id`, `data`, `mes_referencia`, `valor`, `conta`, `descricao`) VALUES
(1, '2021-10-08', 'Janeiro','300', 'Bradesco', 'Descrição do pagamento'),
(2, '2021-10-08', 'Janeiro','400', 'Caixa', 'Descrição do pagamento');

I select it like this:

SELECT (
    (SELECT SUM(`valor`) AS Total
    FROM `lancamentos`)
    -
    (SELECT SUM(`valor`) AS Total
    FROM `pagamentos`)
  )As Total

And I got that result 410.7

How do I get this: R$ 410,70 ??

[Code] http://sqlfiddle.com/#! 9/d67c6aa/1

Grateful!!

Answer 1

The database stores "numbers", if you want to show in another format you can do this later, with some programming language, to display in a report or page.

If you still need to format at query level in the database, you can use the format function, passing through decimal places and culture:

select FORMAT(410.7, 2, 'pt_BR')

See working here: http://sqlfiddle.com/

If you want the monetary prefix, just concatenate like this:

 select CONCAT('R$ ', FORMAT(410.7, 2, 'pt_BR'));

Answer 2

I did it that way:

SELECT res.Total, 
CONCAT('R$ ' , FORMAT(res.Total, 2, 'de_DE')) AS Valor
FROM (
SELECT (
    (SELECT SUM(`valor`) AS Total
    FROM `lancamentos`)
    -
    (SELECT SUM(`valor`) AS Total
    FROM `pagamentos`)
)As Total ) res
   

Complete code: http://sqlfiddle.com/#! 9/d67c6aa/12