'Group using data from one query into another

I have a table that looks like below. It is created using a query -

NPI   Other_Columns
123   Several_Other_Columns
456   Several_Other_Columns

How do I take every NPI from this table and get a count of the number of times they appeared in another table? The structure of the other table is like so -

Claim_id  NPI1  NPI2  NPI3  NPI4  NPI5  NPI6  NPI7  NPI8

If NPIs in the first table, show in any field in the second table, we want to count that claim.s

sqlpostgresqlsnowflake-cloud-data-platform


Solution 1:[1]

The first task is the join

SELECT
   t1.npi,
   t1.other_columns,
   t2.claim_id
FROM table1 as t1
JOIN table2 as t2 ON t1.npi in (t2.np1,t2.np2,t2.np3,t2.np4,t2.np5,t2.np6,t2.np7,t2.np8)

that gets you all the things joined.

Now count those..

SELECT
   count(t2.claim_id)
FROM table1 as t1
JOIN table2 as t2 ON t1.npi in (t2.np1,t2.np2,t2.np3,t2.np4,t2.np5,t2.np6,t2.np7,t2.np8)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Simeon Pilgrim

Related Questions