Solved

single row

Posted on 2006-06-26
8
510 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:gokhanoz
  • 4
  • 4
8 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 125 total points
ID: 16987052
SELECT GROUP_CONCAT(a, b) FROM (SELECT SUM(field1) - SUM(field2) AS a, field3 AS b FROM t1 GROUP BY field3) AS c;
0
 

Author Comment

by:gokhanoz
ID: 16987272
ok thanks, but I mean in seperate colums, like;

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

Expert Comment

by:snoyes_jw
ID: 16987286
Are you only ever going to have these three strings, or do you need it to automatically support yen and pesos and whatever else?
0
 

Author Comment

by:gokhanoz
ID: 16987316
no, there might be much more than these three strings. So it should support the future needs.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16987392
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).
0
 

Author Comment

by:gokhanoz
ID: 16987538
ok, just to improve my knowledge, suppose that there is only 3 string values, how can I get the result as I wrote above?
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 16987563
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
0
 

Author Comment

by:gokhanoz
ID: 16990019
thanks...
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now