using Query one results to run Query two

Question

    SELECT distinct
    Pd.Cpd as ' accountnumber'
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id

This code would result with this

Result

accountnumber  
1332132
3213123
5641202
6412221
1233242

What I would like to do is when the code above gets the results my bottom code reads them and runs them trough its code. The common denominator here would be the account number because its running through a different table

 SELECT 
    pm.AcctNumb as 'accountnumber'
    , SUM(CASE WHEN pm.cusidIN ('cr') THEN 1 ELSE 0 END) AS CA
    , SUM(CASE WHEN pm.cusidIN ('gb') THEN 1 ELSE 0 END) AS GB
    , SUM(CASE WHEN pm.cusidIN ('tev','offev','Lastev') THEN 1 ELSE 0 END) AS chr
    , SUM(CASE WHEN pm.cusidIN 
    ('pm','pr','che' )
    THEN 1 ELSE 0 END) AS Act
    , SUM(CASE WHEN pm.cusidIN ('supev','tev') THEN 1 ELSE 0 END) AS Fulfillment
 FROM ops.medadata pm WITH (NOLOCK)
  INNER JOIN mw.pim_acct Ma with (nolock) ON ma.AcctNumb= pm.AcctNumb
Where pm.AcctNumb in ()
  GROUP BY 
 pm.AcctNumb

I have tried doing this the code below but it doesnt seem to work

With counta as (

SELECT distinct
Pd.Cpd as ' accountnumber'
FROM [RQL_ALK_PMT].[Cts_opps] pd
INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
)
SELECT 
pm.AcctNumb as 'accountnumber'
, SUM(CASE WHEN pm.cusidIN ('cr') THEN 1 ELSE 0 END) AS CA
, SUM(CASE WHEN pm.cusidIN ('gb') THEN 1 ELSE 0 END) AS GB
, SUM(CASE WHEN pm.cusidIN ('tev','offev','Lastev') THEN 1 ELSE 0 END) AS chr
, SUM(CASE WHEN pm.cusidIN 
('pm','pr','che' )
THEN 1 ELSE 0 END) AS Act
, SUM(CASE WHEN pm.cusidIN ('supev','tev') THEN 1 ELSE 0 END) AS Fulfillment
 FROM ops.medadata pm WITH (NOLOCK)
  INNER JOIN mw.pim_acct Ma with (nolock) ON ma.AcctNumb= pm.AcctNumb
left join counta on Pm.accountnumber = counta.accountnumber
Where pm.AcctNumb in (counta.accountnumber)
  GROUP BY 
 pm.AcctNumb

**im having issue with joining  the two tables together** 

Answer 1

IN is not an equivalent to a join - but you are treating that way.

Instead think of "IN this LIST" and the list could be supplied by you, or by a subquery e.g.

a list given by the query itself

select * from atable
where acol IN ('a','b','c') -- i.e. the list is hardcoded

or, using a subquery

SELECT ...etc.
WHERE pm.AcctNumb IN (
    SELECT Pd.Cpd
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
    )

or, if using a CTE

With counta as (
    SELECT Pd.Cpd
    FROM [RQL_ALK_PMT].[Cts_opps] pd
    INNER JOIN [RQL_ALK_PMT].[Cts_opps].dpo.cnms_id metg on metg.cnms_id=me.cnms_id
    )
SELECT ... etc.
WHERE pm.AcctNumb IN (
    SELECT Cpd
    FROM counta
    )

note, it usually is not more efficient to use select distinct when forming a subquery to be used with an IN list.