[WD12] UNION Query

Hello to you all,

Let me explain what I want to achieve. I want the results of two sum's in one record. let's say I've the following records:
Account Amount
1300 1000
1300 100
1300 10
1300 -100
1300 -1
1100 500
1100 -5
1100 50
Now I need a query where I can the following result:
Account AmountD AmountC
1100 550 -5
1300 1110 -101

Of course I can use an UNION for 2 query. The first one to count the positive amounts and the second to count the negative amounts. But then the result will be 4 records:
Account Amount
1100 550
1300 1110
1100 -5
1300 -101

Can anybody tell me how my query should look like?

Thanks in advance.

You need an extra SELECT wrapping your UNION

something (not tested) like:

SELECT account, SUM(positive),SUM(negative) FROM
(
SELECT account, SUM(amount) as positive, 0 as negative FROM table WHERE SUM(amount)>0 GROUP BY account
UNION
SELECT account, 0 as positive, SUM(amount) as negative, FROM table WHERE SUM(amount)<0 GROUP BY account
)
GROUP BY account

MSSQL and Oracle can do this. Not sure about HFCS.

Arie

Hi Louis:

You are not telling us, wich SQL engine are you using. But in SQL Server you can do the following:

SELECT account,
SUM(case when amount >=0 then amount else 0 end) as debit,
SUM(case when amount >=0 then 0 else -amount end) as credit
from Table
GROUP BY account
ORDER BY account

Hope it helps

Andres Sanchez
Monterrey Mexico

Thanks guys,

I'll give them a try. I just use HF and HFCS.



Edited 1 time(s). Last edit at 10/09/2008 08:40AM by Louis Verbraak.

Hello Arie,

Your code works for HF as well. Now I've a followup question. I want to read form another file the description of the accountnumber. I thought I could use the LEFT OUTER JOIN, but I can't get it work.

This is what I was thinking of:

SELECT account, Accountfile.Description, SUM(positive),SUM(negative) FROM
Accountfile LEFT OUTER JOIN
(
SELECT account, SUM(amount) as positive, 0 as negative FROM table WHERE SUM(amount)>0 GROUP BY account
UNION
SELECT account, 0 as positive, SUM(amount) as negative, FROM table WHERE SUM(amount)<0 GROUP BY account
)

ON Accountfile.KeyID=account

GROUP BY account

This gives indeed the description of the account. Only the accounts for which there is an amount are found. I hoped the LEFT OUTER JOIN would give me all the accounts. I tried several possiblities (LEFT, RIGHT, OUTER) with no avail.

Can you point me in the right direction?

Thanks in advance.

Louis,
LEFT OUTER JOIN should do the trick. I did a little test myself - same result. It;s acting like a default INNER JOIN.
So I'm afraid I can't help you. Maybe the HF engine does not support this. You could ask pcsoft.

Arie

Hello Arie,

Thanks again. I'll solve it another way.