How_to_code_summary_queries/exercise_1.sql

SELECT vendor_id, SUM(invoice_total)
FROM invoices
GROUP BY vendor_id;

SELECT v.vendor_name, SUM(i.payment_total) AS payment_total_sum
FROM vendors v
JOIN invoices i
ON v.vendor_id = i.vendor_id
GROUP BY vendor_name
ORDER BY payment_total_sum DESC;

SELECT vendor_name,
Count(*) AS invoice_count, SUM(invoice_total) AS invoice_total_sum
FROM vendors v
JOIN invoices i
ON v.vendor_id = i.vendor_id
GROUP BY vendor_name
ORDER BY invoice_count;

SELECT account_description, Count(*) AS line_item_count, Sum(line_item_amount) AS line_item_amount_sum
FROM General_Ledger_Accounts g
JOIN Invoice_Line_items li
on g.account_number= li.account_number
JOIN invoices i
ON i.invoice_id = li.invoice_id
WHERE invoice_date BETWEEN '2018-04-01' AND '2018-06-30'
GROUP BY account_description
HAVING line_item_count > 1
ORDER BY line_item_amount_sum DESC;

SELECT account_number, SUM(line_item_amount) AS line_item_amount_sum
FROM Invoice_Line_items
GROUP BY account_number WITH ROLLUP;

SELECT vendor_name, COUNT( DISTINCT account_number) AS number_of_accounts
FROM vendors v
JOIN invoices i
ON v.vendor_id = i.vendor_id
JOIN Invoice_Line_Items li
ON i.invoice_id = li.invoice_id

GROUP BY vendor_name
HAVING number_of_accounts > 1;