change output type to currency

Question

I have a code that gives me two columns, Expense and Total, what I can not get is that in the output of the totals it gives me in currency format (R $ 1,000,00). the code is this:

SELECT    nome_evento AS Despesa, SUM(saida_caixa) AS total
FROM      genius.caixa
LEFT JOIN genius.eventos ON eventos.id = id_despesa
WHERE     `data_caixa` BETWEEN "2016-07-29" AND "2016-08-30"
AND       `saida_caixa`!= 0
GROUP BY  id_despesa;

Answer 1

It is unclear which of the columns you want to apply formatting to, so I’ll show you a generic shape:

SELECT FORMAT(12332.2, 2,'pt_BR');

The result is 12332,20. (Without the points)

Reference: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_format

Explaining better

The first parameter is the value, the second is the number of decimals. The third is optional, where you can set the locality.
In the example I applied the site to Brazilian Portuguese.

In your case I assume it would look like this:

FORMAT(SUM(saida_caixa), 2,'pt_BR') AS total

Suggestion

I don’t know where you want to display the data, but if you are going to display it on an HTML page, I suggest thinking about leaving the processing cost to the client, with Javascript. See this link: /a/81554/4793

I emphasize that it is a superficial suggestion and does not mean that it is the best form nor the only one. The best form depends on each case.