CountAccessionsAgentSource.sql

SELECT 
    COUNT(DISTINCT accession.id)
FROM
    accession
        JOIN
    linked_agents_rlshp ON linked_agents_rlshp.accession_id = accession.id
        LEFT JOIN
    agent_person ON linked_agents_rlshp.agent_person_id = agent_person.id
        LEFT JOIN
    agent_corporate_entity ON linked_agents_rlshp.agent_corporate_entity_id = agent_corporate_entity.id
        LEFT JOIN
    name_person ON name_person.agent_person_id = agent_person.id
        LEFT JOIN
    name_corporate_entity ON name_corporate_entity.agent_corporate_entity_id = agent_corporate_entity.id
        LEFT JOIN
    enumeration_value evrole ON linked_agents_rlshp.role_id = evrole.id
WHERE
    evrole.value = 'source';