Prev: Help with Query
Next: find word greater than 17
From: Prasath on 23 Apr 2008 08:35
i have a table with 4 columns, they look like

currency1 currency2 amt1 amt2
=======================
USD GBP 10 9
GBP USD 5 8
JPY INR 20 7
INR JPY 14 100

i want to cross add the amounts for each group of currency1 and
currency2. now, the currency groups are bit unusal. For example,
the first two rows in the above table fall under the group USD+GBP (or
GBP+USD, the other does not matter)

the result should look like

currency1 currency2 sum1 sum2
USD GBP 18 14 (from 10+8 and 9+5)
INR JPY 120 21 (from 20+100 and 14+7)

any ideas?
From: Ed Prochak on 23 Apr 2008 08:50
On Apr 23, 7:35 am, Prasath <prasath....(a)gmail.com> wrote:
> i have a table with 4 columns, they look like
>
> currency1 currency2 amt1 amt2
> =======================
> USD GBP 10 9
> GBP USD 5 8
> JPY INR 20 7
> INR JPY 14 100
>
> i want to cross add the amounts for each group of currency1 and
> currency2. now, the currency groups are bit unusal. For example,
> the first two rows in the above table fall under the group USD+GBP (or
> GBP+USD, the other does not matter)
>
> the result should look like
>
> currency1 currency2 sum1 sum2
> USD GBP 18 14 (from 10+8 and 9+5)
> INR JPY 120 21 (from 20+100 and 14+7)
>
> any ideas?

what have you tried?
I can imagine a simple UNION may help. Then you will need a way to
weed out the "duplicates", e.g.
USD GBP 18 14
GBP USD 14 18
Show us what you tried and then we can help.
(This is to avoid doing someone's homework for them.)

Awaiting your reply,
ed
From: Prasath on 23 Apr 2008 08:57
On Apr 23, 1:50 pm, Ed Prochak <edproc...(a)gmail.com> wrote:
> On Apr 23, 7:35 am, Prasath <prasath....(a)gmail.com> wrote:
>
>
>
> > i have a table with 4 columns, they look like
>
> > currency1 currency2 amt1 amt2
> > =======================
> > USD GBP 10 9
> > GBP USD 5 8
> > JPY INR 20 7
> > INR JPY 14 100
>
> > i want to cross add the amounts for each group of currency1 and
> > currency2. now, the currency groups are bit unusal. For example,
> > the first two rows in the above table fall under the group USD+GBP (or
> > GBP+USD, the other does not matter)
>
> > the result should look like
>
> > currency1 currency2 sum1 sum2
> > USD GBP 18 14 (from 10+8 and 9+5)
> > INR JPY 120 21 (from 20+100 and 14+7)
>
> > any ideas?
>
> what have you tried?
> I can imagine a simple UNION may help. Then you will need a way to
> weed out the "duplicates", e.g.
> USD GBP 18 14
> GBP USD 14 18
> Show us what you tried and then we can help.
> (This is to avoid doing someone's homework for them.)
>
> Awaiting your reply,
> ed

the below query works.

select LEAST(currency1,currency2) first, GREATEST(currency1,currency2)
second,
SUM(case when currency1 = greatest(currency1,currency2) then amt1 else
amt2 end) amt1,
SUM(case when currency1 = least(currency1,currency2) then amt1 else
amt2 end) amt2
from test
group by LEAST(currency1,currency2), GREATEST(currency1,currency2)
 | 
Pages: 1
Prev: Help with Query
Next: find word greater than 17