151 lines
5.6 KiB
SQL
151 lines
5.6 KiB
SQL
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; |