sqlh10.3

-- Opdracht 1 | Products

/* Toon per vend_id het aantal producten vanaf 3. */

-- vend_id	num_prods
-- BRS01   	3
-- DLL01   	4

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 3;

-- Opdracht 2 | Products

/* Breid opdracht 1 uit met een filter op de prod_price vanaf 4. */

-- vend_id	num_prods
-- BRS01   	3

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 3;

-- Opdracht 3 | Products

/* Toon per vend_id de totale prijs kleiner dan 20. Sorteer aflopend op de totale prijs. */

-- vend_id		total
-- FNG01     	18.98
-- DLL01     	15.46

SELECT vend_id, SUM(prod_price) AS total 
FROM Products
GROUP BY vend_id 
HAVING SUM(prod_price) [kleinerdanteken] 20
ORDER BY total DESC;

-- Opdracht 4 | OrderItems

/* Toon per prod_id het percentage van het totaal aantal bestelde artikelen. */

--	prod_id		prod_percent
--	BNBG01    	25.17 %
--	BNBG02    	25.17 %
--	BNBG03    	25.17 %
--	BR01      	8.39 %
--	BR02      	0.70 %
--	BR03      	11.54 %
--	RGAN01    	3.85 %

SELECT prod_id, FORMAT (CONVERT(numeric, SUM(quantity)) / CONVERT(numeric,(SELECT SUM(quantity) FROM OrderItems)), 'P') AS prod_percent
FROM OrderItems
GROUP BY prod_id;

-- MySQL
SELECT prod_id, SUM(quantity) / (SELECT SUM(quantity) FROM OrderItems) AS prod_percent
FROM OrderItems
GROUP BY prod_id;