CODEWITHSUNDEEP
mysqlsqldatabaseconcatenation
Jim Dover
Jim Dover

Reputation: 623

MySQL concat result of two selects

I am using the following query in MySQL two sum a field in two tables into one figure:

SELECT 
(SELECT sum(number)
FROM table1 
WHERE mydate IS NOT NULL) +
(SELECT sum(number)
FROM table2 
WHERE mydate IS NOT NULL)
AS sumOfTheTwo

This is working fine and sumOfTheTwo is giving me my correct result.

The problem is, is that I want to output this number as currency. Normally I would use the following:

concat("$", format(sumOfTheTwo, 2))

I can't figure out how I would do it in this case. Could anyone suggest anything?

Thanks!

EDIT / SOLUTION:

As: Oto Shavadze showed, the following resolved this:

SELECT concat("$", format( 

(SELECT sum(number)
FROM table1 
WHERE mydate IS NOT NULL) +
(SELECT sum(number)
FROM table2 
WHERE mydate IS NOT NULL)

, 2)) as res

Upvotes: 1

Views: 2626

Answers (7)

Nakesh
Nakesh

Reputation: 576

Use concate() with first select and use format() to add number of zeros after decimal(.) . 2 in formate() indicates 2 zero after decimal.

select concat("$", format(
(SELECT sum(number) FROM table1 WHERE mydate IS NOT NULL) + 
(SELECT sum(number) FROM table1 WHERE mydate IS NOT NULL), 2)) 
as sumOfTheTwo`

Upvotes: 0

Gagan Sharma
Gagan Sharma

Reputation: 220

In sql server i have an example :

 SELECT 
 '$'+
cast((SELECT 1) + (SELECT 2) as varchar(50)) AS sumOfTheTwo

Upvotes: 0

Amit
Amit

Reputation: 142

SELECT CONCAT('$',((SELECT sum(number)
FROM table1 
WHERE mydate IS NOT NULL) +
(SELECT sum(number)
 FROM table2 
 WHERE mydate IS NOT NULL)));

Upvotes: 0

Shadow
Shadow

Reputation: 34232

Just use concat() with your current query:

SELECT 
concat('$', (SELECT sum(number)
FROM table1 
WHERE mydate IS NOT NULL) +
(SELECT sum(number)
FROM table2 
WHERE mydate IS NOT NULL))
AS sumOfTheTwo

Pls note that it may be a lot more efficient to perform this formatting in the application logic, rather than in sql.

Upvotes: 1

A1Gard
A1Gard

Reputation: 4168

You must Concat in first block I try this and worked for me:

SELECT 

 concat("$", format(
    (
    -- block 1
    (SELECT sum(number)
    FROM table1 
    WHERE mydate IS NOT NULL) +
    -- block 2
    (SELECT sum(number)
    FROM table2 
    WHERE mydate IS NOT NULL)

     ), 2))

AS sumOfTheTwo ;

it so simple.

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42753

SELECT concat("$", format( 

    (SELECT sum(number)
    FROM table1 
    WHERE mydate IS NOT NULL) +
    (SELECT sum(number)
    FROM table2 
    WHERE mydate IS NOT NULL)

, 2)) as res

Upvotes: 1

TazGPL
TazGPL

Reputation: 3748

Have a look at the MySQL string format functions. FORMAT(), CONCAT() and CONCAT_WS() in particular.

FORMAT lets you convert a string into a number, while CONCAT will be use to append a currency symbol.

Upvotes: 1

Related Questions