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 to_char(a.created_at,'yyyy') = '2022'
-- 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 to_char(a.created_at,'yyyy') = '2022'
-- 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 to_char(a.created_at,'yyyy') = '2022'
-- 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 to_char(a.created_at,'yyyy') = '2022'
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 to_char(a.created_at,'yyyy') = '2022'
-- and c.name = 'Permintaan Data Aplikasi'
group by c.name
) h on (c.name=h.name)
WHERE
to_char(a.created_at,'yyyy') = '2022'
and rating = 1
and TO_CHAR(a.created_at,'YYYY-MM-DD') >= '01-01-2022' and TO_CHAR(a.created_at,'YYYY-MM-DD') <= '31-12-2022'
-- and c.name = 'Permintaan Data Aplikasi'
group by c.name, sangat_puas, puas, cukup, tidak_puas, sangat_tidak_puas
) a