Complete SQL Mastery - Creating Views

SQL
1

Can someone explain to me why when I’m Creating a View, once I enter the sum of invoice minus the payment history I can’t see the actual invoice or payment totals. The Code works as described by Mosh, I just don’t get the reasoning. See below. I expanded on Mosh’s example.

USE sql_invoicing;
CREATE VIEW clients_balance AS
SELECT
c.client_id,
c.name,
– i.invoice_total, – Why this doesn’t this work after I enter the next couple lines of code?
– i.payment_total, – Why this doesn’t this work after I enter the next line of code?
SUM(invoice_total - payment_total) AS BALANCE
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id

2

You are querying groups of invoices, not individual invoices and try to query the values of individual invoices.

Say you are querying a table with cars and group it by brands. What colour does each brand have?

What you can do is to query the sum of invoice_total and payment_total of the invoices in the group:

SELECT
c.client_id,
c.name,
SUM(i.invoice_total),
SUM(i.payment_total),
SUM(invoice_total - payment_total) AS BALANCE
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id
3

Thank you that makes sense