Running Total; 7. Joining on Conditions (Continuation)

Q&A DQ Courses
#1

The Instructions are to Write a query that displays one invoice per row with the following data:

The invoice ID
The date of the invoice
The total of the invoice
The running total for the invoice, rounded to two decimal places aliased as running_total

When I run the below code, I beleive I get the asnwer required for the excercise however it’s not being accepted… what is the reason this is incorrect?

  SELECT i1.invoice_id, i1.invoice_date, i1.total, ROUND(SUM(i2.total), 2) AS running_total
     FROM invoice i1
      JOIN invoice i2
         ON i1.invoice_id >= i2.invoice_id AND i1.invoice_date >= i2.invoice_date
GROUP BY i1.invoice_id;

I’m not getting the understanding from the lessons THE WHY that provides logic for certain information belonging with particular caluses.
As far as I understand, I have grouped the invoice row to meet the requirement to display one invoice per row.

Your GROUP BY clause is using less expressions than expected. It should have 3 expressions in total but only has 1.

I’ve found that the correct answer is
SELECT i1.invoice_id, i1.invoice_date, i1.total, ROUND(SUM(i2.total), 2) AS running_total
FROM invoice i1
JOIN invoice i2
ON i1.invoice_id >= i2.invoice_id AND i1.invoice_date >= i2.invoice_date
GROUP BY i1.invoice_id, i1.invoice_date, i1.total;

Thanks in Advance

#2

What that means is that the rows in the sql table are categorized by order ID. The table now has all distinct order id, followed by all attributes that belong to each order id. Then they are grouped by invoice id. The table now has all distinct order ids, followed by all distinct invoice ids within each order ids along with all attributes that belong to those. Then the table is grouped by the total. The table now has all distinct order ids, followed by all distinct invoice ids within those, followed by all distinct totals within those. Then the other attributes follow.

1 Like