Postgres query issue [duplicate]

Question

The db schema along with sample data is something like this -

(Country table)

| Country | Country Code | 
--------------------------
    ABC         A 
    BCD         B

(TransactionTable)

| SrcCountryCode | DestCountryCode| SrcCurrency| DestCurrency | SrcAmount | DestAmount |
----------------------------------------------------------------------------------------
         A                B              X            Y           200         1000
         A                B              X            Y           300         1500
         B                A              Y            X           1000         200

I want the result set to be like this -

| Corridor | Total Src Amount| Total Dest Amount | Src Currency |
-----------------------------------------------------------------
  ABC-BCD          500              200                 X
  BCD-ABC         1000              2500                Y

I am lost on how to map the Country combinations as well as map the total amounts for destination and source subsequently in one table. Help would be appreciated.

Didn't you post this somewhere else a couple of days ago? It looks familiar. — Craig Ringer
– Craig Ringer, Commented Jul 3, 2015 at 4:44
Yup. If you want to follow up on a post because comments/discussion on the original haven't gone anywhere, please make sure the new post links to the old one and you make it clear what issue(s) were not addressed by the original post, rather than just re-posting verbatim. — Craig Ringer
– Craig Ringer, Commented Jul 3, 2015 at 4:45
@CraigRinger Well, this time he's looking for totaling numbers. Though that's sort of SQL 101: use sum. I'm writing an answer but now I'm wondering if it'll truly help... — Dan Getz
– Dan Getz, Commented Jul 3, 2015 at 4:47
That is weird. A few days ago, got an answer that "worked", and today, "lost" as to how to do the same thing? I guess you'll need to be more clear in questions as to what you're really asking about, and what you're really confused about. — Dan Getz
– Dan Getz, Commented Jul 3, 2015 at 5:03

zedfoxus · Accepted Answer · 2015-07-03 04:47:01Z

1

This sql query like this

select 
    d.Country+'-'+e.Country as Corridor, 
    TotalSrcAmount, 
    TotalDestAmount, 
    SrcCurrency
from TransactionTable a

join
(
    select SrcCurrency ,sum(SrcAmount) 'TotalSrcAmount'
    from TransactionTable 
    Group by SrcCurrency 
)b on a.SrcCurrency =b.SrcCurrency 

join
(
    select DestCurrency,sum(DestAmount) 'TotalDestAmount'
    from TransactionTable
    Group by DestCurrency
)c on a.SrcCurrency =c.DestCurrency

join Countrytable d on d.Country_Code=a.SrcCountryCode

join Countrytable e on e.Country_Code=a.DestCountryCode

group by 
    d.country,
    e.country,
    a.SrcCurrency

edited Jul 3, 2015 at 4:47

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

answered Jul 3, 2015 at 4:43

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

davyjones Over a year ago

I got the solution I was looking for. Can you please also explain how this query is actually getting executed? Thanks.

Collectives™ on Stack Overflow

Postgres query issue [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related