laporan layanan query

select
name,
jumlah_keluhan,
sangat_puas,
puas, cukup,
tidak_puas,
sangat_tidak_puas,
substr( cast(( cast((sangat_puas*5) + (puas*4) + (cukup*3) + (tidak_puas*2) + (sangat_tidak_puas*1) as decimal ) /
jumlah_keluhan) as varchar),1,3 ) rating
from (
SELECT
c.name, count(1) jumlah_keluhan, coalesce(sangat_puas,0) sangat_puas, coalesce(puas,0) puas, coalesce(cukup,0) cukup,
coalesce(tidak_puas,0) tidak_puas, coalesce(sangat_tidak_puas,0) sangat_tidak_puas
FROM
tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
LEFT JOIN
(
select c.name, count(1) sangat_puas
from tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
where rating = 1 and score = 5




-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) d on (c.name=d.name)
LEFT JOIN
(
select c.name, count(1) puas
from tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
where rating = 1 and score = 4




-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) e on (c.name=e.name)
LEFT JOIN
(
select c.name, count(1) cukup
from tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
where rating = 1 and score = 3




-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) f on (c.name=f.name)
LEFT JOIN
(
select c.name, count(1) tidak_puas
from tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
where rating = 1 and score = 2




-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) g on (c.name=g.name)
LEFT JOIN
(
select c.name, count(1) sangat_tidak_puas
from tickets
a JOIN sub_service b ON ( a.sub_service_id = b.ID )
JOIN services C ON ( b.service_id = C.ID )
where rating = 1 and score = 1




-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) h on (c.name=h.name)
-- JOIN ticket_status_history tsh ON ( tsh.ticket_id = a.id)
WHERE
rating = 1





-- and c.name = 'Permintaan Data Aplikasi'
group by c.name, sangat_puas, puas, cukup, tidak_puas, sangat_tidak_puas
) a