You can use the DPQL_MATRIX function in the GROUP BY clause to create Matrix tables in DPQL.

A particularly common example might be where you're using DPQL_COUNT in your SELECT statement and you'd like to divide the count up by two fields.

Using GROUP BY

You may want to view the number of tickets solved broken down by category.

We could do this with the query below:

SELECT DPQL_COUNT() AS 'Tickets by category'
FROM tickets
WHERE tickets.status IN('resolved', 'archived')
GROUP BY tickets.category
 copy

This will give us a count of solved tickets that were assigned to that category.

Using a Matrix in GROUP BY

We might want to take that a step further though and see how many tickets are being resolved by each agent under that category.

This is where we would use the DPQL_MATRIX function

SELECT DPQL_COUNT() AS 'Tickets by category'
FROM tickets
WHERE tickets.status IN('resolved', 'archived')
GROUP BY DPQL_MATRIX(tickets.category, tickets.agent)
 copy

This will create a Matrix table such as the example below: