How to fix “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error?

Question

My query is as follows, and contains a subquery within it:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from xxxx_snapshot.dbo.bill_prvdr_acct_history a
where a.acct_id in 
(select acct_id, count(*)
from xxxx_snapshot.dbo.bill_prvdr_acct_history 
group by acct_id
having count(*) > 1)

and i get the error message "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Answer 1

Given the structure of your query, window functions are probably an easier method to do what you want:

select a.bill_prvdr_acct_id, a.acct_id, a.bill_prvdr_acct_strt_dt, a.bill_prvdr_acct_end_dt
from (select a.*, count(*) over (partition by acct_id) as cnt
      from xxxx_snapshot.dbo.bill_prvdr_acct_history a
     ) a
where cnt > 1;