Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

single row

Posted on 2006-06-26
8
Medium Priority
?
519 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 500 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:gokhanoz
ID: 16987316
no, there might be much more than these three strings. So it should support the future needs.
0
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

885 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