SELECT
	ael.ArticleEntry_Language_Title AS `Verkaufsartikel`,
	COUNT(DISTINCT sai.ShopArticleItem_ID) AS `Varianten`,
	COUNT(DISTINCT aep.Picture_ID) AS `Bilder insgesamt`,
	COUNT(DISTINCT aep.Picture_ID) - COUNT(DISTINCT saip.ShopArticleItem_ID) AS `Allgemeine Bilder`,
	COUNT(DISTINCT saip.ShopArticleItem_ID) AS `Variantenbilder`,
	COUNT(DISTINCT sai.ShopArticleItem_ID) - COUNT(DISTINCT saip.ShopArticleItem_ID) AS `Fehlende Variantenbilder`
FROM ShopArticle AS sa
INNER JOIN ArticleEntry AS ae ON (ae.ArticleEntry_ID = sa.ArticleEntry_ID)
INNER JOIN ArticleEntry_Language AS ael ON (ael.ArticleEntry_ID = ae.ArticleEntry_ID AND ael.Language_Code = 'de')
INNER JOIN ShopArticleItem AS sai ON (sai.ShopArticle_ID = sa.ShopArticle_ID)
LEFT JOIN ArticleEntry_Picture AS aep ON (aep.ArticleEntry_ID = ae.ArticleEntry_ID)
LEFT JOIN ShopArticleItem_Picture AS saip ON (saip.ShopArticleItem_ID = sai.ShopArticleItem_ID)
WHERE
	sa.ShopArticle_Show = 'true'
	AND sai.ShopArticleItem_Show = 'true'
	AND IFNULL(ael.ArticleEntry_Language_Show, 'true') = 'true'
GROUP BY sa.ShopArticle_ID
HAVING
	`Varianten` > 1
	AND `Fehlende Variantenbilder` > 0
ORDER BY `Fehlende Variantenbilder` DESC
