Complex Postgres query

Question

I have a db schema like following -

(Country table)
| Country | Country Code| 
-------------------------
    ABC         A 
    BCD         B

(Organization Table)

|Organization | Country Code | Organization Code

Org 1            A                O1
Org 2            B                O2
Org 3            A                O3

(Transaction Table)

| Organization | Export(in $) | Import(in $)|

 O1             X1                Y1
 O2             X2                Y2
 O3             X3                Y3

I want the result set to be like this -

| Corridor | Total Export | Total Import |
------------------------------------------
  ABC-BCD      X1+X2+X3       Y1+Y2+Y3

Corridor column should be the combination of all the countries in the Country table.

How can I form a query to implement this logic? Thanks

How do you define the field Corridor? Is it the combination of all the countries or only the 1st two countries or what? — Marlon Abeykoon
– Marlon Abeykoon, Commented Jul 2, 2015 at 4:05
Corridor column should be the combination of all the countries in the Country table. — davyjones
– davyjones, Commented Jul 2, 2015 at 4:15
What do you mean by "a combination of all the countries in the Country table"? Do you mean each country combination has one entry? What does each row in your result set represent? — Mike K
– Mike K, Commented Jul 3, 2015 at 8:12
Incidentally, the question title "Complex Postgres query" is not very specific. I don't think your query is likely to be particularly complex, but you might do better to highlight what about it is giving you most trouble. Is it the many-many table join? Summing totals? Defining the question? — Mike K
– Mike K, Commented Jul 3, 2015 at 8:16

cha · Accepted Answer · 2015-07-02 04:21:38Z

1

All you need to do is to run an aggregate query:

select sum(t.export) as TotalExport,
sum(t.import) as TotalImport
FROM country c inner join Organization o on c.Country_Code = o.Country_Code
inner join Transaction t on o.organization_code = t.organization_code

Now, you ask: where is the Corridor column? The answer is: use the string_agg function:

select string_agg(DISTINCT c.country, '-' ORDER BY c.country) as Corridor,
sum(t.export) as TotalExport,
sum(t.import) as TotalImport
FROM country c inner join Organization o on c.Country_Code = o.Country_Code
inner join Transaction t on o.organization_code = t.organization_code

answered Jul 2, 2015 at 4:21

cha

10.4k1 gold badge20 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Complex Postgres query

|Organization | Country Code | Organization Code

| Organization | Export(in $) | Import(in $)|

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

|Organization | Country Code | Organization Code

| Organization | Export(in $) | Import(in $)|

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related