Re: Query using group

On Apr 23, 1:50 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Apr 23, 7:35 am, Prasath <prasath...._at_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) Received on Wed Apr 23 2008 - 07:57:29 CDT