Postingan lainnya
Query untuk menggabungkan menjadi 1 data
Hallo temen-temen semua,
saya punya query seperti ini :
SELECT
DATE_FORMAT(tglinput_si,'%m') as tgl,
sum(if(DATE_FORMAT(tglinput_si,'%m')='01',grandtot,0)) AS jan,
sum(if(DATE_FORMAT(tglinput_si,'%m')='02',grandtot,0)) AS feb,
sum(if(DATE_FORMAT(tglinput_si,'%m')='03',grandtot,0)) AS mar,
sum(if(DATE_FORMAT(tglinput_si,'%m')='04',grandtot,0)) AS apr,
sum(if(DATE_FORMAT(tglinput_si,'%m')='05',grandtot,0)) AS may,
sum(if(DATE_FORMAT(tglinput_si,'%m')='06',grandtot,0)) AS jun,
sum(if(DATE_FORMAT(tglinput_si,'%m')='07',grandtot,0)) AS jul,
sum(if(DATE_FORMAT(tglinput_si,'%m')='08',grandtot,0)) AS ags,
sum(if(DATE_FORMAT(tglinput_si,'%m')='09',grandtot,0)) AS sep,
sum(if(DATE_FORMAT(tglinput_si,'%m')='10',grandtot,0)) AS okt,
sum(if(DATE_FORMAT(tglinput_si,'%m')='11',grandtot,0)) AS nov,
sum(if(DATE_FORMAT(tglinput_si,'%m')='12',grandtot,0)) AS des
FROM
pastibisa_service_so
WHERE
tglinput_si !=''
GROUP BY
tgl
union
SELECT
DATE_FORMAT(tanggal,'%m') as tgl,
sum(if(DATE_FORMAT(tanggal,'%m')='01',total,0)) AS jan,
sum(if(DATE_FORMAT(tanggal,'%m')='02',total,0)) AS feb,
sum(if(DATE_FORMAT(tanggal,'%m')='03',total,0)) AS mar,
sum(if(DATE_FORMAT(tanggal,'%m')='04',total,0)) AS apr,
sum(if(DATE_FORMAT(tanggal,'%m')='05',total,0)) AS may,
sum(if(DATE_FORMAT(tanggal,'%m')='06',total,0)) AS jun,
sum(if(DATE_FORMAT(tanggal,'%m')='07',total,0)) AS jul,
sum(if(DATE_FORMAT(tanggal,'%m')='08',total,0)) AS ags,
sum(if(DATE_FORMAT(tanggal,'%m')='09',total,0)) AS sep,
sum(if(DATE_FORMAT(tanggal,'%m')='10',total,0)) AS okt,
sum(if(DATE_FORMAT(tanggal,'%m')='11',total,0)) AS nov,
sum(if(DATE_FORMAT(tanggal,'%m')='12',total,0)) AS des
FROM
service_pengeluaran_part
GROUP BY tgl
dan hasilnya seperti link gambar berikut ini : <a href='https://drive.google.com/file/d/1xKDIgelU_ky3HZa7jEkd2SALsLxytfAE/view'>capture1.png</a>
nah saya ingin mengabungkan jadi satu data tersebut termasuk jumlahnya, ada yg bisa bantu engga. terima kasih sebelumnya.
2 Jawaban:
Jawaban Terpilih
Subquery aja..
<pre> SELECT tgl, SUM(jan) AS jan, SUM(feb) AS feb, SUM(mar) AS mar, SUM(apr) AS apr, SUM(may) AS may, SUM(jun) AS jun, SUM(jul) AS jul, SUM(ags) AS ags, SUM(sep) AS sep, SUM(okt) AS okt, SUM(nov) AS nov, SUM(des) AS des FROM ( SELECT DATE_FORMAT(tglinput_si,'%m') as tgl, sum(if(DATE_FORMAT(tglinput_si,'%m')='01',grandtot,0)) AS jan, sum(if(DATE_FORMAT(tglinput_si,'%m')='02',grandtot,0)) AS feb, sum(if(DATE_FORMAT(tglinput_si,'%m')='03',grandtot,0)) AS mar, sum(if(DATE_FORMAT(tglinput_si,'%m')='04',grandtot,0)) AS apr, sum(if(DATE_FORMAT(tglinput_si,'%m')='05',grandtot,0)) AS may, sum(if(DATE_FORMAT(tglinput_si,'%m')='06',grandtot,0)) AS jun, sum(if(DATE_FORMAT(tglinput_si,'%m')='07',grandtot,0)) AS jul, sum(if(DATE_FORMAT(tglinput_si,'%m')='08',grandtot,0)) AS ags, sum(if(DATE_FORMAT(tglinput_si,'%m')='09',grandtot,0)) AS sep, sum(if(DATE_FORMAT(tglinput_si,'%m')='10',grandtot,0)) AS okt, sum(if(DATE_FORMAT(tglinput_si,'%m')='11',grandtot,0)) AS nov, sum(if(DATE_FORMAT(tglinput_si,'%m')='12',grandtot,0)) AS des FROM pastibisa_service_so WHERE tglinput_si !='' GROUP BY tgl
union
SELECT
DATE_FORMAT(tanggal,'%m') as tgl,
sum(if(DATE_FORMAT(tanggal,'%m')='01',total,0)) AS jan,
sum(if(DATE_FORMAT(tanggal,'%m')='02',total,0)) AS feb,
sum(if(DATE_FORMAT(tanggal,'%m')='03',total,0)) AS mar,
sum(if(DATE_FORMAT(tanggal,'%m')='04',total,0)) AS apr,
sum(if(DATE_FORMAT(tanggal,'%m')='05',total,0)) AS may,
sum(if(DATE_FORMAT(tanggal,'%m')='06',total,0)) AS jun,
sum(if(DATE_FORMAT(tanggal,'%m')='07',total,0)) AS jul,
sum(if(DATE_FORMAT(tanggal,'%m')='08',total,0)) AS ags,
sum(if(DATE_FORMAT(tanggal,'%m')='09',total,0)) AS sep,
sum(if(DATE_FORMAT(tanggal,'%m')='10',total,0)) AS okt,
sum(if(DATE_FORMAT(tanggal,'%m')='11',total,0)) AS nov,
sum(if(DATE_FORMAT(tanggal,'%m')='12',total,0)) AS des
FROM
service_pengeluaran_part
GROUP BY tgl
) temp GROUP BY tgl </pre>
Semoga terbantu
