DPQL_COUNT_DISTINCT

List of functions

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'

I get the following output:

avg6.png

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'

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'

This returns 18 which is correct.

AVG7.png

Published: 17/07/2019

Last updated: 26/07/2019