-1

I have two tables:

currency_table_1

ID  -  currency_1
------------------
01  -   EUR
02  -   EUR
03  -   EUR
04  -   USD
05  -   USD
06  -   USD

currency_table_2

ID  -  currency_2
------------------
01  -   EUR
02  -   EUR
04  -   JPY
05  -   JPY
06  -   JPY
07  -   JPY

I want to FULL OUTER JOIN the two tables on the ID. In the result table I like to add a COUNT column which sums the ocurrences of unique currency combinations of the two tables. If there is no ID/currency in the other table, respectively, the currency value in that combination will show as [null]. For above example the result table would look like:

Count -  currency_1  -  currency_2
----------------------------------
3     -     USD      -    JPY
2     -     EUR      -    EUR
1     -     EUR      -    [null]
1     -     [null]   -    JPY

How does the sql look like? I am familiar with group by and joins, but didn't have success on that one so far. Thanks for your input!

Timo
  • 3
  • 1
  • Please don't ask us to write your code. Show what you are able to do. Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] – philipxy May 22 '20 at 03:47

1 Answers1

1

I think this is what you want:

select ct1.currency_1, ct2.currency_2, count(*)
from currency_table1 ct1 full join
     currency_table2 ct2
     on ct1.id = ct2.id
group by ct1.currency_1, ct2.currency_2;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786