SELECT
	saai.ShopArticleAttributeItem_Name AS `Marke`,
	SUM(IFNULL(soi.ShopOrderItem_Count, 0)) AS `Produktmenge`,
 	REPLACE(SUM(IFNULL(soi.ShopOrderItem_Count, 0) * IFNULL(soi.ShopOrderItem_PriceExcl, 0.0)),  '.', ',') AS `Umsatz (netto)`
FROM ShopArticleAttributeItem AS saai
LEFT JOIN ShopArticleAttribute AS saa ON (saa.ShopArticleAttributeItem_ID = saai.ShopArticleAttributeItem_ID)
LEFT JOIN ShopArticle AS sa ON (saa.ShopArticle_ID = sa.ShopArticle_ID)
LEFT JOIN ShopArticleItem AS sai ON (sai.ShopArticle_ID = sa.ShopArticle_ID)
LEFT JOIN ShopOrderItem AS soi ON (sai.ShopArticleItem_ID = soi.ShopArticleItem_ID)
LEFT JOIN ShopOrder AS so ON (so.ShopOrder_ID = soi.ShopOrder_ID)
WHERE
	saai.ShopArticleAttributeType_ID = 1
	AND (so.ShopOrderType_Code IS NULL OR so.ShopOrderType_Code = 'order')
	AND (so.ShopOrderState_Code IS NULL OR so.ShopOrderState_Code != 'new')
GROUP BY saai.ShopArticleAttributeItem_ID
ORDER BY SUM(soi.ShopOrderItem_Count) DESC, SUM(soi.ShopOrderItem_Count * soi.ShopOrderItem_PriceExcl) DESC