Solved

# single row

Posted on 2006-06-26
509 Views
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
Question by:gokhanoz
• 4
• 4

LVL 33

Accepted Solution

snoyes_jw earned 125 total points
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

ok thanks, but I mean in seperate colums, like;

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

LVL 33

Expert Comment

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

no, there might be much more than these three strings. So it should support the future needs.
0

LVL 33

Expert Comment

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

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

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

thanks...
0

## Join & Write a Comment Already a member? Login.

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!