Daftar dan Belajar Sekarang!

Query untuk menggabungkan menjadi 1 data

foto sugiarto27
2 Kontribusi 0 Coto
1 minggu yang lalu
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 : capture1.png nah saya ingin mengabungkan jadi satu data tersebut termasuk jumlahnya, ada yg bisa bantu engga. terima kasih sebelumnya.

Tag: mysql

✔ Forum ini sudah terjawab


Jawaban
foto meij0hn
2 Kontribusi 2 Coto
1 minggu yang lalu
Subquery aja..
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
Semoga terbantu
answered icon
foto meij0hn
2 Kontribusi 2 Coto
1 minggu yang lalu
Subquery aja..
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
Semoga terbantu

foto sugiarto27
2 Kontribusi 0 Coto
1 minggu yang lalu
terima kasih @meij0hn sudah bisa.


Login untuk diksusi di forum sekolah koding