Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Allen Pitts

ASKER

Looks like I needed to nest the group by select. Thanks.
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