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 £


using mysql 5.018
Who is Participating?
snoyes_jwConnect With a Mentor Commented:
SELECT GROUP_CONCAT(a, b) FROM (SELECT SUM(field1) - SUM(field2) AS a, field3 AS b FROM t1 GROUP BY field3) AS c;
gokhanozAuthor Commented:
ok thanks, but I mean in seperate colums, like;

   col1          col2            col3
287,9$ |  -230.12 €  |  -43,12 £
Are you only ever going to have these three strings, or do you need it to automatically support yen and pesos and whatever else?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

gokhanozAuthor Commented:
no, there might be much more than these three strings. So it should support the future needs.
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).
gokhanozAuthor Commented:
ok, just to improve my knowledge, suppose that there is only 3 string values, how can I get the result as I wrote above?
  (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
gokhanozAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.