We help IT Professionals succeed at work.

SQL Server Sum with Join

Allen Pitts
Allen Pitts asked
on
96 Views
Last Modified: 2018-04-13
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
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Allen PittsBusiness analyst

Author

Commented:
Looks like I needed to nest the group by select. Thanks.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.