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'