Example Query MySQL

QUERY_EXAMPLE.sql

SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names) as jumlah_excel, (SELECT COUNT(DISTINCT data_master_control.id_cspro) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names) as jumlah_cspro, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_clean_data = 'OK') as jumlah_data_clean FROM provinsi


SELECT * FROM data_master_control where data_master_control.provinsi_excel = 'DKI Jakarta - UMUM' GROUP BY data_master_control.nama_krt_excel

SELECT provinsi.id, provinsi.province_names as prov_id, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_clean_data = 'OK') as jumlah_data_clean FROM provinsi

SELECT * from data_master_control group by id_excel


SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = "" Order By data_master_control.id_excel DESC) as data_revisi FROM provinsi

SELECT * from data_master_control where provinsi_excel = 'Sumatera Utara' GROUP BY data_master_control.id_excel


SELECT provinsi.id, provinsi.province_names as province_names FROM provinsi


create view coba2 as SELECT * from data_master_control GROUP BY data_master_control.id_excel order by data_master_control.id = 'DESC'


select count(id) as qwe from coba2 where provinsi_excel = 'Sumatera Utara' AND status_ldui = ''

SELECT SUM(column_name)
FROM table_name
WHERE condition;


SELECT provinsi.id, provinsi.province_names as province_names, (select count(id) as qwe from coba2 where provinsi_excel = province_names AND status_ldui = '' order by id = 'DESC') as total  FROM provinsi




SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = "" Order By data_master_control.id_excel DESC) as data_revisi FROM provinsi


SELECT data_master_control.* from data_master_control where provinsi_excel = 'Sumatera Utara' GROUP BY data_master_control.id_excel ORDER BY id DESC

SELECT data_master_control.*
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.id_excel
) AND provinsi_excel = 'Sumatera Utara';


SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_ldui = '' order by id = 'DESC') as data_revisi  FROM provinsi


SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_ldui = '' order by id = 'DESC') as data_revisi, 
(SELECT data_master_control.batch_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as batch_kirim, 
(SELECT data_master_control.tanggal_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as tanggal_pengiriman FROM provinsi




SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_ldui = '' order by id = 'DESC') as data_revisi, 
(SELECT data_master_control.batch_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as batch_kirim, 
(SELECT data_master_control.tanggal_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as tanggal_pengiriman, (SELECT SUM(total_data) as data_baru
FROM (SELECT data_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = province_names) as data_baru FROM provinsi


SELECT count(*)
FROM (
   SELECT COUNT(id_excel) AS count
   FROM data_master_control
   WHERE provinsi_excel = 'Sumatera Utara'
   GROUP BY id_excel
   HAVING (count = 1)
   
) AS x

SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_bi = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_bi = '' order by id = 'DESC') as data_revisi, 
(SELECT data_master_control.batch_pengiriman_bi
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as batch_kirim, 
(SELECT data_master_control.tanggal_pengiriman_bi
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as tanggal_pengiriman, (SELECT SUM(total_data) as data_baru
FROM (SELECT data_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = province_names) as data_baru, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control where provinsi_excel = province_names AND kota_excel IS NOT NULL) as jumlah_excel,(SELECT COUNT(DISTINCT data_master_control.id_cspro) FROM data_master_control where provinsi_cspro = province_names AND kota_cspro IS NOT NULL
) as jumlah_cspro FROM provinsi


#####################
#####################
#####################
#####################
#####################  2 2 2 2 2 2 2
#####################
#####################
#####################
#####################
#####################

SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_ldui = 'NOT_OK' order by id = 'DESC') as data_revisi, 
(SELECT data_master_control.batch_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as batch_kirim, 
(SELECT data_master_control.tanggal_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as tanggal_pengiriman, (SELECT SUM(total_data) as data_baru
FROM (SELECT data_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = province_names) as data_baru, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control where provinsi_excel = province_names AND kota_excel IS NOT NULL) as jumlah_excel,(SELECT COUNT(DISTINCT data_master_control.id_cspro) FROM data_master_control where provinsi_cspro = province_names AND kota_cspro IS NOT NULL
) as jumlah_cspro, (SELECT MAX(data_master_control.batch_pengiriman_ldui) AS largest_batch FROM data_master_control where provinsi_cspro = province_names) as last_batch FROM provinsi

#DATA BARU BY BATCH
SELECT SUM(total_data) as data_baru, batch_pengiriman_ldui
FROM (SELECT data_master_control.provinsi_excel as province_name, data_master_control.batch_pengiriman_ldui as batch_pengiriman_ldui, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where batch_pengiriman_ldui = 6


#DATA OK BY BATCH
SELECT COUNT(DISTINCT data_master_control.id_excel) as data_ok FROM data_master_control WHERE data_master_control.batch_pengiriman_ldui = 3 AND data_master_control.status_ldui = 'OK' 

#DATA REVISI BY BATCH
select count(id) as data_revisi FROM data_rekap_master_control WHERE status_ldui = 'NOT_OK' AND batch_pengiriman_ldui = 1 order by id = 'DESC'

#CHECK MANY OF BATCH BY PROVINCE 
SELECT data_rekap_master_control.batch_pengiriman_ldui, data_rekap_master_control.tanggal_pengiriman_ldui FROM data_rekap_master_control WHERE data_rekap_master_control.provinsi_excel = 'Sumatera Utara' GROUP BY data_rekap_master_control.batch_pengiriman_ldui

#GET DATA LDUI BY PROVINCE 
SELECT data_master_control.tanggal_pengiriman_ldui, data_master_control.status_pengiriman_ldui FROM data_master_control WHERE data_master_control.provinsi_excel = 'Dki Jakarta - Umum' AND data_master_control.batch_pengiriman_ldui = 1 AND data_master_control.id_excel = '10002'



SELECT data_master_control.status_pengiriman_ldui, data_master_control.tanggal_pengiriman_ldui FROM data_master_control WHERE data_master_control.provinsi_excel = 'Dki Jakarta - Umum' AND data_master_control.id_excel = '10001' 

SELECT * FROM data_rekap_master_control WHERE data_rekap_master_control.provinsi_excel = 'Dki Jakarta - Umum' order by id = 'DESC'





#####################
#####################
#####################
#####################
#####################  3 3 3 3 3 3 3 3 3 3 3 
#####################
#####################
#####################
#####################
#####################


update data_master_control set `batch_pengiriman_ldui` = 0 , `status_pengiriman_ldui` = 0 , `tanggal_pengiriman_ldui` = NULL , status_ldui = ""


SELECT provinsi.id, provinsi.province_names as province_names, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control WHERE data_master_control.provinsi_excel = provinsi.province_names AND data_master_control.status_ldui = 'OK') as data_ok, (select count(id) as qwe from data_rekap_master_control where provinsi_excel = province_names AND status_ldui = 'NOT_OK' order by id = 'DESC') as data_revisi, 
(SELECT data_master_control.batch_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as batch_kirim, 
(SELECT data_master_control.tanggal_pengiriman_ldui
FROM data_master_control
WHERE data_master_control.id IN (
    SELECT MAX(data_master_control.id)
    FROM data_master_control
    GROUP BY data_master_control.provinsi_excel
) AND provinsi_excel = province_names) as tanggal_pengiriman, (SELECT SUM(total_data) as data_baru
FROM (SELECT data_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = province_names) as data_baru, (SELECT COUNT(DISTINCT data_master_control.id_excel) FROM data_master_control where provinsi_excel = province_names AND kota_excel IS NOT NULL) as jumlah_excel,(SELECT COUNT(DISTINCT data_master_control.id_cspro) FROM data_master_control where provinsi_cspro = province_names AND kota_cspro IS NOT NULL
) as jumlah_cspro, (SELECT MAX(data_master_control.batch_pengiriman_ldui) AS largest_batch FROM data_master_control where provinsi_cspro = province_names) as last_batch FROM provinsi

SELECT SUM(total_data) as data_baru, batch_pengiriman_ldui, province_name
FROM (SELECT data_master_control.provinsi_excel as province_name, data_master_control.batch_pengiriman_ldui as batch_pengiriman_ldui, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = 'Dki Jakarta - Umum'

SELECT SUM(total_data) as data_baru
FROM (SELECT data_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_master_control GROUP BY id_excel HAVING total_data = 1) as faka where province_name = 'Dki Jakarta - Umum'

SELECT data_rekap_master_control.batch_pengiriman_bi, data_rekap_master_control.tanggal_pengiriman_bi FROM data_rekap_master_control WHERE data_rekap_master_control.provinsi_excel = 'Dki Jakarta - Umum' GROUP BY data_rekap_master_control.provinsi_excel




SELECT count(id) as data_revisi FROM data_rekap_master_control WHERE status_bi = 'NOT_OK' AND batch_pengiriman_bi = 1 order by id = 'DESC'

########


SELECT id, province_names, target, (SELECT SUM(perolehan) FROM perolehan WHERE perolehan.province_id = id) as data_perolehan, (SELECT SUM(jumlah_balance_sheet_dikirim) FROM penerimaan_data WHERE penerimaan_data.nama_provinsi = province_names AND penerimaan_data.jenis_data = 'Excel') as data_excel, (SELECT SUM(jumlah_balance_sheet_diterima) FROM penerimaan_data WHERE penerimaan_data.nama_provinsi = province_names AND penerimaan_data.jenis_data = 'Excel') as data_excel_diterima, (SELECT SUM(jumlah_kuesioner_dikirim) FROM penerimaan_data WHERE penerimaan_data.nama_provinsi = province_names AND penerimaan_data.jenis_data = 'Kuesioner') as data_kuesioner, (SELECT SUM(jumlah_kuesioner_diterima) FROM penerimaan_data WHERE penerimaan_data.nama_provinsi = province_names AND penerimaan_data.jenis_data = 'Kuesioner') as data_kuesioner_diterima, (SELECT SUM(total_data) as data_ke_ldui
FROM (SELECT data_rekap_master_control.tanggal_pengiriman_ldui as tanggal_pengiriman_ldui, data_rekap_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where province_name = province_names AND tanggal_pengiriman_ldui IS NOT NULL) as data_ke_ldui, (SELECT SUM(total_data) as data_clean_ldui
FROM (SELECT data_rekap_master_control.tanggal_pengiriman_ldui as tanggal_pengiriman_ldui, data_rekap_master_control.provinsi_excel as province_name, data_rekap_master_control.status_ldui as status_ldui, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where province_name = province_names AND status_ldui = 'OK') as data_clean_ldui, (SELECT SUM(total_data) as data_ke_bi
FROM (SELECT data_rekap_master_control.tanggal_pengiriman_bi as tanggal_pengiriman_bi, data_rekap_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where province_name = province_names AND tanggal_pengiriman_bi IS NOT NULL) as data_ke_bi, (SELECT SUM(total_data) as data_ke_bi
FROM (SELECT data_rekap_master_control.batch_pengiriman_bi as batch_pengiriman_bi, data_rekap_master_control.tanggal_pengiriman_bi as tanggal_pengiriman_bi, data_rekap_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where batch_pengiriman_bi = 1 AND province_name = province_names AND tanggal_pengiriman_bi IS NOT NULL) as ke_bi_tahap_1, (SELECT SUM(total_data) as data_ke_bi
FROM (SELECT data_rekap_master_control.batch_pengiriman_bi as batch_pengiriman_bi, data_rekap_master_control.tanggal_pengiriman_bi as tanggal_pengiriman_bi, data_rekap_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where batch_pengiriman_bi = 2 AND province_name = province_names AND tanggal_pengiriman_bi IS NOT NULL) as ke_bi_tahap_2, (SELECT SUM(total_data) as data_ke_bi
FROM (SELECT data_rekap_master_control.batch_pengiriman_bi as batch_pengiriman_bi, data_rekap_master_control.tanggal_pengiriman_bi as tanggal_pengiriman_bi, data_rekap_master_control.provinsi_excel as province_name, COUNT(*) as total_data FROM data_rekap_master_control GROUP BY id_excel) as faka where batch_pengiriman_bi = 3 AND province_name = province_names AND tanggal_pengiriman_bi IS NOT NULL) as ke_bi_tahap_3 FROM provinsi