SELECT 
    ma.*,
    qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
    qs.name, 
    qd.question_label, qd.question_value,
    JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
    JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
    count(qa.answer) as total_agama
FROM mst_agamas as ma
LEFT JOIN question_answers as qa ON qa.answer = ma.keterangan 
LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
LEFT JOIN question_details as qd ON qs.id = qd.question_id
WHERE qa.answer_type = 'Master'
GROUP BY ma.keterangan -- , qa.answer
HAVING 
    question_type = 'Master' 
    AND name_master = 'Agamas';    

-- SHOW SPECIFIC DATA ARRAY
SELECT JSON_UNQUOTE(JSON_EXTRACT('["Agamas", "keterangan"]', '$[0]')) AS name;

-- Trial show all data agama
SELECT 
    qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
    qs.name, 
    qd.question_label, qd.question_value,
    JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
    JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
    count(qa.answer) as total_agama
FROM question_answers as qa 
LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
LEFT JOIN question_details as qd ON qs.id = qd.question_id
WHERE qa.answer_type = 'Master'
GROUP BY qa.answer
HAVING 
    question_type = 'Master' 
    AND name_master = 'Agamas'; 

-- Count data Agama
SELECT 
    ma.keterangan as nama, IFNULL(total.total_agama, 0) as total_agamane
FROM mst_agamas as ma
LEFT JOIN (
    SELECT 
        qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
        qs.name, 
        qd.question_label, qd.question_value,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
        count(qa.answer) as total_agama
    FROM question_answers as qa 
    LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
    LEFT JOIN question_details as qd ON qs.id = qd.question_id
    WHERE qa.answer_type = 'Master'
    GROUP BY qa.answer
    HAVING 
        question_type = 'Master' 
        AND name_master = 'Agamas'
) as total ON total.answer = ma.keterangan;


-- Count data disabilitas
SELECT 
    md.keterangan as nama, IFNULL(total.total_agama, 0) as total_agamane
FROM mst_disabilitas as md
LEFT JOIN (
    SELECT 
        qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
        qs.name, 
        qd.question_label, qd.question_value,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
        count(qa.answer) as total_agama
    FROM question_answers as qa 
    LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
    LEFT JOIN question_details as qd ON qs.id = qd.question_id
    WHERE qa.answer_type = 'Master'
    GROUP BY qa.answer
    HAVING 
        question_type = 'Master' 
        AND name_master = 'MasterDisabilitas'
) as total ON total.answer = md.keterangan;


-- Count data disabilitas
SELECT 
    md.keterangan as nama, IFNULL(total.total_agama, 0) as total_agamane
FROM mst_disabilitas as md
LEFT JOIN (
    SELECT 
        qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
        qs.name, 
        qd.question_label, qd.question_value,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
        count(qa.answer) as total_agama
    FROM question_answers as qa 
    LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
    LEFT JOIN question_details as qd ON qs.id = qd.question_id
    WHERE qa.answer_type = 'Master'
    GROUP BY qa.answer
    HAVING 
        question_type = 'Master' 
        AND name_master = 'MasterDisabilitas'
) as total ON total.answer = md.keterangan;


-- Count data status perkawinan
SELECT 
    msp.keterangan as nama, IFNULL(total.total_agama, 0) as total_agamane
FROM mst_status_perkawinans as msp
LEFT JOIN (
    SELECT 
        qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
        qs.name, 
        qd.question_label, qd.question_value,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
        count(qa.answer) as total_agama
    FROM question_answers as qa 
    LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
    LEFT JOIN question_details as qd ON qs.id = qd.question_id
    WHERE qa.answer_type = 'Master'
    GROUP BY qa.answer
    HAVING 
        question_type = 'Master' 
        AND name_master = 'MasterStatusPerkawinans'
) as total ON total.answer = msp.keterangan;


-- Total Laki Laki Dan Perempuan
SELECT 
    msp.keterangan as nama, IFNULL(total.total_agama, 0) as total_agamane
FROM mst_status_perkawinans as msp
LEFT JOIN (
    SELECT 
        qa.id as question_answer_id, qa.question_id, qa.answer_type, qa.answer,
        qs.name, 
        qd.question_label, qd.question_value,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_label, '$[0]')) AS question_type,
        JSON_UNQUOTE(JSON_EXTRACT(qd.question_value, '$[0]')) AS name_master,
        count(qa.answer) as total_agama
    FROM question_answers as qa 
    LEFT JOIN question_surveys as qs ON qs.id = qa.question_id
    LEFT JOIN question_details as qd ON qs.id = qd.question_id
    WHERE qa.answer_type = 'Master'
    GROUP BY qa.answer
    HAVING 
        question_type = 'Master' 
        AND name_master = 'MasterStatusPerkawinans'
) as total ON total.answer = msp.keterangan;