SubQuery within a select statement POSTGRESQL

Question

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

Note: there exists a date_trunc() function; you dont have to use these ugly string-concatenations. Plus: some formatting would help your query's readability. — wildplasser
– wildplasser, Commented Aug 9, 2021 at 14:47

Ken · Accepted Answer · 2021-08-11 01:12:32Z

1

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

answered Aug 11, 2021 at 1:12

Ken

571 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SubQuery within a select statement POSTGRESQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related