query chart kepuasan layanan

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