Solved

single row

Posted on 2006-06-26
8
511 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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