DPQL_COUNT_DISTINCT counts unique not NULL rows.

Example

A frequent use case for using the DPQL_COUNT_DISTINCT function is when we are building queries using the ticket logs table.

I would like to count every ticket that has been assigned from one agent to another .

If I run the query below:

SELECT DPQL_COUNT(tickets_logs.ticket.id) AS 'Tickets where agent has been reassigned'
FROM tickets_logs
WHERE tickets_logs.action_type = 'changed_agent'
 copy

I get the following output:

However if I run the following query to list out the ticket IDs the list only includes 18 IDs:

SELECT tickets_logs.ticket.id
FROM tickets_logs
WHERE tickets_logs.action_type = 'changed_agent'
 copy

This is because the query actually counts the logs that fit the criteria rather than tickets and multiple logs for 'changed agent' can be created for each ticket (as they can have their assignment changed multiple times).

Therefore we would use the query below to ensure each ID is only counted once

SELECT DPQL_COUNT_DISTINCT(tickets_logs.ticket.id) AS 'Tickets where agent has been reassigned'
FROM tickets_logs
WHERE tickets_logs.action_type = 'changed_agent'
 copy

This returns 18 which is correct.