Marketing Cloud SQL - Invalid Column Name

Question

After I added the Group by statement I got an error that now is saying the column name is invalid, but it does match with the table. Any other ideas of what might be causing the error?

ERROR: An error occurred while checking the query syntax. Errors: Invalid column name 'Department__c'.

SELECT a.PersonContactId as "ContactId",a.PersonEmail as "Email",a.FirstName 
FROM
[Account_Salesforce] a
INNER JOIN (
        SELECT ContactID__c, MAX(Department__c) as "Department"
        FROM [OpportunityLineItem_Salesforce]
        GROUP BY ContactID__c) o 
ON o.ContactID__c = a.PersonContactId  
AND a.PersonHasOptedOutOfEmail = 'FALSE'
AND NOT(ISNULL(a.FirstName,'')='') 
AND NOT(ISNULL(a.PersonEmail,'')='')
AND NOT(ISNULL(o.Department__c,'')='')
AND o.Department__c = 'WEIGHT LOSS MEDICATION'

Answer 1

The issue is your o table fields.

Change o.department__c to o.department

SELECT a.PersonContactId as "ContactId",a.PersonEmail as "Email",a.FirstName 
FROM
[Account_Salesforce] a
INNER JOIN (
        SELECT ContactID__c, MAX(Department__c) as "Department"
        FROM [OpportunityLineItem_Salesforce]
        GROUP BY ContactID__c) o 
ON o.ContactID__c = a.PersonContactId  
AND a.PersonHasOptedOutOfEmail = 'FALSE'
AND NOT(ISNULL(a.FirstName,'')='') 
AND NOT(ISNULL(a.PersonEmail,'')='')
AND NOT(ISNULL(o.Department,'')='')
AND o.Department = 'WEIGHT LOSS MEDICATION'