Avatar of Allen Pitts
Allen PittsFlag for United States of America asked on

SQL Server Sum with Join

Hello expert,

This query works
SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue)
from
ISWareHouse.dbo.CCMProtocolComponents
group by CCMPayerID

and returns a list of revenue by Payers

and this query works
SELECT ISOps.CCM.CCM_PAYER.PAYER_ID,
ISOps.CCM.CCM_PAYER.PAYER_NAME
from ISOps.CCM.CCM_PAYER

Returns Payer_IDs and PAYER_NAMES

Need to join the SELECT(SUM) query
with a PAyer_NAme join query on the PAyer_IDs

Have tried

SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue),
ISOps.CCM.CCM_PAYER.PAYER_NAME
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

returns
The multi-part identifier "ISOps.CCM.CCM_PAYER.PAYER_NAME" could not be bound. (sic)

Have also tried
SELECT ISOps.CCM.CCM_PAYER.PAYER_NAME, SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue),
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

Returns
Incorrect syntax near the keyword 'from'.

Tried just doing the join w/o the name display
SELECT SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue) as Rev
from
ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID

This runs with no error

I think the error in the 'cannot be bound' fetch has to do with the syntax of
ISOps.CCM.CCM_PAYER.PAYER_NAME
but if it works in the straight select statement why does it not work in the
SELECT(SUM) query?

Thanks.

Allen in Dallas
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Allen Pitts

Looks like I needed to nest the group by select. Thanks.
Scott Pletcher

You're welcome.

That's the most efficient.  You could also do this, but it's many more payer_name lookups:

SELECT MAX(ISOps.CCM.CCM_PAYER.PAYER_NAME) AS PAYER_NAME, SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue) AS Revenue
from ISWareHouse.dbo.CCMProtocolComponents
JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = ISWareHouse.dbo.CCMProtocolComponents.CCMPayerID
group by CCMPayerID
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck