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
LVL 1
Allen PittsBusiness analystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
SELECT PAY.PAYER_NAME, revs.Rev
FROM (
    SELECT CCMPayerID, SUM(ISWareHouse.dbo.CCMProtocolComponents.T12Revenue) AS Rev
    from ISWareHouse.dbo.CCMProtocolComponents
    group by CCMPayerID
) as revs
INNER JOIN ISOps.CCM.CCM_PAYER PAY ON PAY.PAYER_ID = Revs.CCMPayerID
--ORDER BY PAYER_NAME /*optional*/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Allen PittsBusiness analystAuthor Commented:
Looks like I needed to nest the group by select. Thanks.
0
Scott PletcherSenior DBACommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.