USE ap;

-- 1. calculate an aggregated function on all records in the invoices table 

SELECT 
    COUNT(*)
FROM
    invoices;

SELECT 
    COUNT(DISTINCT vendor_id)
FROM
    invoices;

-- do not remove duplicates
SELECT 
    COUNT(vendor_id) as vendor_instances
from
    invoices;

-- 2. USE the DISTINCT keyword in an SQL statement
SELECT DISTINCT
    terms_id
from
    invoices;
-- determine the number of vendors for each terms_id 
SELECT 
    terms_id, COUNT(DISTINCT vendor_id) as vendor_nums
from
    invoices
group by terms_id;

-- 3. calculate an aggregated function on some records in the invoices table 

SELECT 
    COUNT(*) AS number_of_invoices,
    SUM(invoice_total - payment_total - credit_total) AS total_due
FROM
    invoices
WHERE
    invoice_totaL - payment_total - credit_total > 0;


-- 4. calculate an aggregated function on groups of records 
-- SELECT field_list needs to contain the group by field_list 
SELECT 
    vendor_id, sum(payment_total)
from
    invoices
GROUP BY vendor_id;

-- count the number of distinct vendor ids appearing in table 
SELECT 
    COUNT(DISTINCT vendor_id) as vendor_nums
from
    invoices;




-- 5. identify the invoices that are above the average payment total and return 
-- the amont they are above the average payment
USE ex;
SELECT 
    invoice_id,
    invoice_date,
    payment_total - (select 
            AVG(payment_total)
        from
            paid_invoices) aboveAvg
FROM
    paid_invoices
where
    payment_total > (SELECT 
            AVG(payment_total)
        FROM
            paid_invoices);

-- 6. Create a table of the paid_invoices that payment_total > average payment
CREATE TABLE above_avg SELECT invoice_id, invoice_date, payment_total FROM
    paid_invoices
where
    payment_total > (SELECT  AVG(payment_total) FROM paid_invoices); 

-- 7. Use the HAVING clause to limit tuples in your aggregated results
-- hint: HAVING allows you to create a filter for the aggregated data
use ap;
SELECT 
    vendor_id, sum(payment_total)
from
    invoices
group by vendor_id
HAVING sum(payment_total) > 150;

-- 8.How many invoices were placed from the different vendor state?
-- Take a look at the JOIN results and make sure you get the correct number of records from the JOIN
SELECT vendor_state, invoice_id FROM vendors AS v LEFT JOIN invoices AS i
ON v.vendor_id = i.vendor_id;
SELECT vendor_state, COUNT(invoice_id) state_invoices FROM vendors AS v LEFT JOIN invoices AS i
ON v.vendor_id = i.vendor_id GROUP BY vendor_state ORDER BY state_invoices DESC;


-- 9 List vendor name, vendor id where their invoice totals are greater 1000
SELECT vendor_name, v.vendor_id, SUM(invoice_total) 
FROM  vendors v JOIN invoices i ON v.vendor_id = i.vendor_id
GROUP BY vendor_name, v.vendor_id HAVING SUM(invoice_total) > 1000;

-- 10. Sum the total payments and credits for account # 400
SELECT SUM(credit_total), SUM(payment_total) FROM invoices i JOIN invoice_line_items li
ON li.invoice_id = i.invoice_id WHERE account_number = 400;

SELECT * from invoice_line_items WHERE account_number = 400;

-- 11. How many times was each product ordered om database?
USE om;
-- why the use of a left join?
SELECT items.item_id, COUNT(*) FROM items LEFT JOIN order_details ON order_details.item_id = items.item_id 
GROUP BY item_id;


-- 12. Which vendors have 3 or more invoices?
USE ap;

SELECT vendor_id, COUNT(*) FROM invoices GROUP BY vendor_id HAVING COUNT(*) > 3;
SELECT vendor_name FROM vendors WHERE vendor_id IN ( 
SELECT vendor_id FROM invoices GROUP BY vendor_id HAVING COUNT(*) > 3);

-- 13. Which line items have a total sales volume of more than $1,000,
SELECT invoice_id, invoice_sequence, line_item_amount FROM invoice_line_items i
WHERE line_item_amount > 1000;

-- 14. List the OrderDates where the line items ordered is at Least 2.
USE om;
SELECT DISTINCT order_date FROM ( 
SELECT o.order_date, o.order_id, COUNT(*) FROM orders o JOIN order_details od ON od.order_id = o.order_id
GROUP BY o.order_date, o.order_id HAVING COUNT(*) >= 2 ) as r;

-- 15. What vendors have not submitted invoices ?
USE ap;
SELECT v.vendor_name, i.vendor_id FROM vendors v 
LEFT JOIN invoices i ON i.vendor_id = v.vendor_id
WHERE i.vendor_id IS NULL;

-- 16. Select invoices for vendors  from California.
SELECT v.vendor_name, i.* FROM vendors v JOIN invoices i ON i.vendor_id = v.vendor_id
WHERE vendor_state = 'CA';


-- 17. List vendor_name and vendor_id that have at least one
--         invoice that uses terms_id = 1. 
SELECT v.vendor_name, i.vendor_id FROM vendors v LEFT JOIN invoices i ON i.vendor_id = v.vendor_id
WHERE i.terms_id = 1 GROUP BY v.vendor_name, i.vendor_id ;