How_to_code_summary_queries/exercise1-9.sql

USE ap;

-- Exercise 1 --
SELECT DISTINCT 
	v.vendor_id, 
    SUM(i.invoice_total) as 'Total of Invoices'
FROM vendors v, invoices i
GROUP BY i.vendor_id;
    
-- Exercise 2 --

SELECT
	vendor_name, SUM(payment_total) AS 'Payment Total Sum'
FROM vendors v INNER JOIN invoices i
	ON v.vendor_id = i.vendor_id
GROUP BY v.vendor_name
ORDER BY SUM(i.payment_total) DESC; 
-- Opletten met SUM in select voor verkeerde data, order by (bijna) altijd met Agg function--

-- Exercise 3--

SELECT
	vendor_name,
    COUNT(invoice_total) AS'Number of Invoices',
    SUM(invoice_total) AS 'Invoice Total'
FROM 
	vendors v INNER JOIN  invoices i 
	ON v.vendor_id = i.vendor_id
GROUP BY v.vendor_name
ORDER BY COUNT(invoice_total) DESC;

-- Exercise 4 -- 

SELECT 
	g.account_description,
    COUNT(line_item_amount) AS 'Number of Items',
    SUM(line_item_amount) AS 'Total of Items'
FROM 
	general_ledger_accounts g INNER JOIN invoice_line_items il
	ON g.account_number = il.account_number
GROUP BY g.account_description
HAVING COUNT(line_item_amount) > 1
ORDER BY SUM(line_item_amount) DESC;

-- Exercise 5--

SELECT
g.account_description,
    COUNT(line_item_amount) AS 'Number of Items',
    SUM(line_item_amount) AS 'Total of Items',
    invoice_date
FROM general_ledger_accounts g INNER JOIN invoice_line_items il
	ON g.account_number = il.account_number 
    INNER JOIN invoices i 
    ON i.invoice_id = il.invoice_id
GROUP BY g.account_number
HAVING count(il.line_item_amount) > 1 
	AND invoice_date >= '2018-04-01' AND invoice_date <='2018-06-30' -- beter in de where plaatsen voor datasnelheid--
ORDER BY SUM(line_item_amount) DESC;

-- Exercise 6 --
SELECT 
	account_number,
    SUM(line_item_amount)
FROM invoice_line_items il
GROUP BY account_number
WITH ROLLUP;

-- Exercise 7 --
SELECT 
	vendor_name,
    COUNT( DISTINCT ili.account_number)
FROM 
	vendors v INNER JOIN invoices i
		ON v.vendor_id = i.vendor_id
        INNER JOIN invoice_line_items ili
			ON i.invoice_id = ili.invoice_id
GROUP BY vendor_name
HAVING COUNT( DISTINCT ili.account_number) > 1;