asked on

single row

Hi, I am trying to execute the following sceriano,

there is a table named table1,
fileds are,
field1 (double)
field2 (double)
field3 (string)

field1 field2 field3
300 0 $
0 230,12 €
0 43,12 £
0 12,1 $

how can I get the sum(field1)- sum(field2) grouping on field3 which returns a single row? I mean the query result should be 1 row like;

287.9 $, -230.12 €, -43,12 £

thanks..

using mysql 5.018

ASKER CERTIFIED SOLUTION

snoyes_jw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gokhanoz

ASKER

ok thanks, but I mean in seperate colums, like;

col1 col2 col3
287,9$ | -230.12 € | -43,12 £

snoyes_jw

Are you only ever going to have these three strings, or do you need it to automatically support yen and pesos and whatever else?

gokhanoz

ASKER

no, there might be much more than these three strings. So it should support the future needs.

snoyes_jw

In that case, I recommend you don't try to get those values in a single row with many columns. Instead, keep them in many rows of two columns (one for value, one for string) and put them in a single column with your application (the PHP script or whatever it is you use to show results).

gokhanoz

ASKER

ok, just to improve my knowledge, suppose that there is only 3 string values, how can I get the result as I wrote above?

snoyes_jw

SELECT
(SELECT CONCAT(SUM(field1) - SUM(field2), field3) FROM table1 WHERE field3 = '$') AS col1,
(SELECT CONCAT(SUM(field1) - SUM(field2), field3) FROM table1 WHERE field3 = '€') AS col2,
(SELECT CONCAT(SUM(field1) - SUM(field2), field3) FROM table1 WHERE field3 = '£') AS col3

gokhanoz

ASKER

thanks...