Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

single row

Posted on 2006-06-26
8
Medium Priority
?
518 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

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 …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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

715 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