SELECT 	m.messe,
				m.von,
				m.bis,
				FORMAT(m.mwst * 100,2) AS MwSt,
				COUNT(k.idpos) AS AnzahlBON,
				SUM(p.Anzahl) AS AnzahlArtikel,
				FORMAT(SUM(p.Anzahl)/COUNT(k.idpos),2) AS Durchschnitt,
				FORMAT(SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount),2) AS VKGesamtBrutto,
				FORMAT(SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)/(1+m.mwst),2) AS VKGesamtNetto,
				FORMAT(SUM(IF(payment='karte',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0)),2) AS SummeKarteBrutto,
				IFNULL(FORMAT(SUM(IF(payment='karte',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0))/SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)*100,2),0) AS PercentKarte,
				FORMAT(SUM(IF(payment='karte',(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)/(1+m.mwst),0)),2) AS SummeKarteNetto,
				FORMAT(SUM(IF(payment='bar',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0)),2) AS SummeBarBrutto,
				IFNULL(FORMAT(SUM(IF(payment='bar',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0))/SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)*100,2),0) AS PercentBar,
				FORMAT(SUM(IF(payment='bar',(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)/(1+m.mwst),0)),2) AS SummeBarNetto,
				FORMAT(SUM(IF(payment='paypal',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0)),2) AS SummePayPalBrutto,
				IFNULL(FORMAT(SUM(IF(payment='paypal',p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount,0))/SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)*100,2),0) AS PercentPayPal,
				FORMAT(SUM(IF(payment='paypal',(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)/(1+m.mwst),0)),2) AS SummePayPalNetto,
				FORMAT(SUM(p.EKSumme)*1.19,2) AS EKGesamtBrutto,
				FORMAT(SUM(p.EKSumme),2) AS EKGesamtNetto,
				FORMAT(SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)-SUM(p.EKSumme)*1.19,2) AS ErtragBrutto,
				FORMAT(SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)/(1+m.mwst)-SUM(p.EKSumme),2) AS ErtragNetto,
				FORMAT(IFNULL((SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount)-SUM(p.EKSumme)*1.19)/(SUM(p.Summe*(100-k.dpercent)/100*(100+k.cpercent)/100-k.damount))*100,0),2) AS PercentErtrag,
				m.idmesse,
				m.closed
		FROM posk k 
		LEFT JOIN messen m ON k.idmesse = m.idmesse
		LEFT JOIN 
			(SELECT idposk, 
					SUM(preis * (100-rabatt)/100 - nachlass) AS Summe, 
					COUNT(idposp) AS Anzahl,
					SUM(ekpreis) AS EKSumme
			 FROM posp
			 GROUP BY idposk) p ON k.idpos = p.idposk 
			 
		WHERE NOT k.deleted AND m.idmesse = 30
		GROUP BY m.messe