quote:

---

Originally posted by JayRU

I am attempting to get a sum for a column I counted before placing in a temp table. I am getting a error message telling me one of th columns is invalid which I don't understand. I am linking one table twice in one table and have given one an alias. Any assistance would be greatly appreciated. Thanks in advance. Below is code and error message.

Create Table #CounselorCounts
(
AgencyName nvarchar (50),
CounselorCount int
)

SELECT clinical.name AS [Name],
COUNT(Clinical.Name) AS CounselorCount

FROM ProviderProvider_ClinicalProviderCounselor

INNER JOIN Clinical ON ProviderProvider_ClinicalProviderCounselor.ProviderCounselor = Clinical.Oid
INNER JOIN Clinical AS Clinical_1 ON ProviderProvider_ClinicalProviderCounselor.Provider = Clinical_1.Oid

Group by Clinical.Name,clinical_1.name

INSERT INTO #CounselorCounts ([AgencyName],CounselorCount)

SELECT AgencyName,
SUM(CounselorCount)AS AgencyCount

FROM #CounselorCounts

GROUP BY AgencyName

DROP TABLE #CounselorCounts

error:

Msg 207, Level 16, State 1, Line 17
Invalid column name 'Name'.

---

SELECT clinical.name AS [Name],
COUNT(Clinical.Name) AS CounselorCount

FROM ProviderProvider_ClinicalProviderCounselor 

INNER JOIN Clinical ON ProviderProvider_ClinicalProviderCounselor.ProviderCounselor = Clinical.Oid 
INNER JOIN Clinical AS Clinical_1 ON ProviderProvider_ClinicalProviderCounselor.Provider = Clinical_1.Oid

Group by Clinical.Name,clinical_1.name

quote:

---

Originally posted by JayRU

I need to provide a count by agency name of counselors in each agency, counselor name is a nvarchar field so I am counting names and grouping by agency name hoping to sum by agency name in the tesmnp table.

---

quote:

---

Originally posted by JayRU

I am not sure, but will try that. Your correction count by the counselor name and not by agency. I am not getting anything inserted into the temp table. Any reason why?

---

quote:

---

Originally posted by JayRU

The second join of the clinical (clinical_1) table was providing the agency name and what I was using to get the agency name.

---