To generate the invoice, we need to join purchases with product prices, group by invoice and product, and sum the quantities and total price for each product in each invoice. Then, sum the total for each invoice.

Approach

Join Purchases with Products on product_id to get the price for each purchase.
Group by invoice_id and product_id to sum the quantity and calculate the total price for each product in each invoice.
For each invoice, sum the total price of all products to get the invoice total.
Output the invoice details: invoice_id, product_id, quantity, product_total, and invoice_total, ordered by invoice_id and product_id.

Code

[{"language":"MySQL","code":"WITH invoice_details AS (\n  SELECT p.invoice_id, p.product_id, SUM(p.quantity) AS quantity,\n         SUM(p.quantity * pr.price) AS product_total\n  FROM Purchases p\n  JOIN Products pr ON p.product_id = pr.product_id\n  GROUP BY p.invoice_id, p.product_id\n),\ninvoice_totals AS (\n  SELECT invoice_id, SUM(product_total) AS invoice_total\n  FROM invoice_details\n  GROUP BY invoice_id\n)\nSELECT d.invoice_id, d.product_id, d.quantity, d.product_total, t.invoice_total\nFROM invoice_details d\nJOIN invoice_totals t ON d.invoice_id = t.invoice_id\nORDER BY d.invoice_id, d.product_id;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">WITH</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> invoice_details </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> (</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">  SELECT</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\"> p</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">invoice_id</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">, </span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">p</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">.</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">product_id</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">, </span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">SUM</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">(</span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">p</span><span style=\"color:#24292

Related Tags