-- Using the ap database
USE ap;
-- 1. Return the invoices for vendor names that start with P
SELECT 
    i . *, vendor_name
FROM
    invoices AS i
        JOIN
    vendors AS v ON v.vendor_id = i.vendor_id
WHERE
    vendor_name LIKE 'P%';
 
-- 2. Count the number of invoices for each vendor, return the vendor_id, vendor_name and the count
SELECT 
    v.vendor_id, v.vendor_name, COUNT(*)
FROM
    vendors v
        JOIN
    invoices i ON v.vendor_id = i.vendor_id
GROUP BY vendor_id , vendor_name;

-- 3. Calculate the total payment by vendor, return the vendor name and the total
SELECT 
    vendor_name, vendors.vendor_id, SUM(payment_total)
FROM
    vendors
        JOIN
    invoices ON vendors.vendor_id = invoices.vendor_id
GROUP BY vendor_name , vendors.vendor_id;

-- 4. Provide a JOIN across the invoice_line_items and the invoices table, 
-- verify the results of invoice_total
-- solution includes an example of a subquery
SELECT 
    invoices.invoice_id, invoice_total, computed_total
FROM
    invoices
        JOIN
    (SELECT 
        invoice_id, sum(line_item_amount) AS computed_total
    from
        invoice_line_items
    GROUP BY invoice_id) c ON c.invoice_id = invoices.invoice_id
WHERE
    computed_total != invoice_total;

-- first verify the result

SELECT 
    invoices.invoice_id,
    invoice_total,
    sum(line_item_amount) AS computed_total
FROM
    Invoice_line_items
        JOIN
    invoices ON invoices.invoice_id = invoice_line_items.invoice_id
GROUP BY invoices.invoice_id ,invoice_total; 

-- then add the restriction 
SELECT 
    invoices.invoice_id,
    invoice_total,
    sum(line_item_amount) AS computed_total
FROM
    Invoice_line_items
        JOIN
    invoices ON invoices.invoice_id = invoice_line_items.invoice_id
GROUP BY invoices.invoice_id ,invoice_total HAVING sum(line_item_amount) != invoice_total;
;

-- 5. Calculate the total payment by vendor, return the vendor id , vendor name  and the total payment 
-- for vendor for vendors who paid more than $100

SELECT 
    COUNT(*)
FROM
    vendors;

-- Next, write the left join query and verify the number of result records 
SELECT 
    sum(payment_total) AS vendor_total,
    vendor_name,
    vendors.vendor_id
FROM
    vendors
        LEFT JOIN
    invoices ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendor_id , vendor_name , vendors.vendor_id
ORDER BY sum(payment_total);

-- many vendors without totals 

SELECT 
    sum(payment_total) AS vendor_total,
    vendor_name,
    vendors.vendor_id
FROM
    vendors
        LEFT JOIN
    invoices ON invoices.vendor_id = vendors.vendor_id
GROUP BY vendors.vendor_name , vendors.vendor_id
HAVING sum(payment_total) > 100
ORDER BY sum(payment_total);