CODEWITHSUNDEEP
postgresqlsubquery
Ken
Ken

Reputation: 57

SubQuery within a select statement POSTGRESQL

I have two different tables (Medical and Rx Data) both that contain the Paid Date and Net Payment. I want to create a report summary by Year and Month of the Total Medical Net Payment and Rx Net Payment. When I run the below SQL, POSTGRESQL says "subquery uses ungrouped column "a.date_paid" from outer query"

My thought was to group by the first table and Subquery the total Net payment from the second table. POSTGRESQL

select cast(EXTRACT(YEAR FROM a.date_paid) as text) || to_char(a.date_paid,'mm') as date_paid,
cast (sum(a.net_payment) as money) as Medical,a.member_ssn,
(Select sum(b.netpayment) from elan.rx_claims b where cast(EXTRACT(YEAR FROM a.date_paid) as text) || 
to_char(a.date_paid,'mm')=cast(EXTRACT(YEAR FROM b.date_paid) as text) || to_char(b.date_paid,'mm')
and a.member_ssn=b.member_ssn ) as RxPaid

from elan.staging_claims a
group by cast(EXTRACT(YEAR FROM a.date_paid) as text) || to_char(a.date_paid,'mm'),a.member_ssn

Upvotes: 0

Views: 2630

Answers (1)

Ken
Ken

Reputation: 57

I was able to resolve this myself...

select  a.date_paid,a.member_ssn,memfirstname,memlastname ,cast (sum(a.net_payment) as money) ,

(Select sum(b.netpayment) from elan.rx_claims b 
 where cast(EXTRACT(YEAR FROM a.date_paid) as text) || to_char(a.date_paid,'mm')=cast(EXTRACT(YEAR FROM b.date_paid)as text) || to_char(b.date_paid,'mm')
 and a.member_ssn=b.member_ssn)

from elan.staging_claims a, elan.elig c
where a.member_ssn=c.employeessn
group by a.date_paid,a.member_ssn,memfirstname,memlastname
having sum(a.net_payment)>79999
order by 2 desc

Upvotes: 1

Related Questions