This is very specific to our list of components and statuses, and relies on
the WikiTableMacro plugin from https://trac-hacks.org/wiki/WikiTableMacro,
but I wrote a query a few years ago that is close to what you are looking
for, I think. This may have some MySQL specific syntax, but should be
close enough to be helpful.
{{{
#!SQLTable
select
s.component as Functional_Area,
concat('[query:?status=new=', replace(s.component, ' ', '+'),
' ', s.new, ']' ) as New,
concat('[query:?status=assigned=', replace(s.component, ' ',
'+'),
' ', s.assigned, ']' ) as Assigned,
concat('[query:?status=in_work=', replace(s.component, ' ',
'+'),
' ', s.in_work, ']' ) as In_Work,
concat('[query:?status=in_QA=', replace(s.component, ' ', '+'),
' ', s.in_QA, ']' ) as In_QA,
concat('[query:?status=info_needed=', replace(s.component, ' ',
'+'),
' ', s.info_needed, ']' ) as Info_Needed,
concat('[query:?status=resolved=', replace(s.component, ' ',
'+'),
' ', s.resolved, ']' ) as Resolved,
concat('[query:?status=closed=', replace(s.component, ' ', '+'),
' ', s.closed, ']' ) as Closed,
concat('[query:?component=', replace(s.component, ' ', '+'),
' ', s.total, ']' ) as Total
from (
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'ar'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'auxiliary'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'bcr'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'ca'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'cm'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'gl / ld / pdp'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when status = 'in_work' then 1 end ) in_work,
count( case when status = 'in_QA' then 1 end ) in_QA,
count( case when status = 'info_needed' then 1 end ) info_needed,
count( case when status = 'resolved' then 1 end ) resolved,
count( case when status = 'closed' then 1 end ) closed,
count(*) as total
from
ticket
where
component = 'pay'
UNION
select
component,
count( case when status = 'new' then 1 end ) new,
count( case when status = 'assigned' then 1 end ) assigned,
count( case when
