Database Queries

select * from kpi_hdc k left join kpi_template i on i.id=k.kpi_template_id left join kpi_template_to_year y on y.kpi_template_id=k.kpi_template_id where y.year='2020' and k.kpi_template_id='11'

SELECT IF(X.areacode!="TOTAL",areaname,"รวมทั้งหมด") as areaname_new, X.* FROM (SELECT IFNULL(C.changwatcode,"TOTAL") as areacode, C.changwatname as areaname, MAX(K.date_com) AS report_date, SUM(K.target) as target, SUM(K.result) as result, ROUND( SUM(IFNULL(K.result,0))/SUM(IFNULL(K.target,0))*100,2 ) as total_ratio, ROUND( SUM(IFNULL(K.result,0))/SUM(IFNULL(K.target,0))*100,2 ) as chart_value, SUM(K.target1) as targetq1, SUM(K.result1) as resultq1, ROUND( SUM(IFNULL(K.result1,0))/SUM(IFNULL(K.target1,0))*100,2 ) as ratioq1, SUM(K.target2) as targetq2, SUM(K.result2) as resultq2, ROUND( SUM(IFNULL(K.result2,0))/SUM(IFNULL(K.target2,0))*100,2 ) as ratioq2, SUM(K.target3) as targetq3, SUM(K.result3) as resultq3, ROUND( SUM(IFNULL(K.result3,0))/SUM(IFNULL(K.target3,0))*100,2 ) as ratioq3, SUM(K.target4) as targetq4, SUM(K.result4) as resultq4, ROUND( SUM(IFNULL(K.result4,0))/SUM(IFNULL(K.target4,0))*100, 2) as ratioq4 FROM s_kpi_anc_iodine K INNER JOIN cchangwat C ON C.changwatcode=LEFT(K.areacode,2) INNER JOIN cregion Z ON Z.region_code=C.regioncode WHERE K.id = '422edc9d4d81d5c943a8ca9029f20e78' AND K.b_year = 2563 and c.regioncode = '02' GROUP BY C.changwatcode WITH ROLLUP) X