Create a query to produce the total purchase per invoice, generating the results

Question

Create a query to produce the total purchase per invoice, generating the results shown in Figure P7.33. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE. Use a query to show the invoices and invoice totals in Figure P7.34. (Hint: Group by the CUS_CODE.) Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure P7.35 as your guide. (Compare this summary to the results shown in Problem 34.) Using the query results in Problem 35 as your basis, write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest invoice amount, the largest invoice amount, and the average of all the invoices. (Hint: Check the figure output in Problem 35.) Your output must match Figure P7.36.

Explanation / Answer

$query = "select * from CUSTOMER"; $result = mysql_query($query); while($row=mysql_fetch_array($result)){ $inv = $row["CUS_CODE"]; $query = "select * from LINE where CUS_CODE=$inv"; $result1 = mysql_query($query); while($row1 = mysql_fetch_array($result1){ $sum+=$row1[CUSTOMER_PRICE]; } $query = "insert into INVOICETOTALS(INV_NUMBER,Total) values($inv,$sum)"; mysql_query($query); } $query = "select * from INVOICE"; $result = mysql_query($query); while($row=mysql_fetch_array($result)){ $inv = $row["INV_NUMBER"]; $query = "select * from LINE where INV_NUMBER=$inv"; $result1 = mysql_query($query); while($row1 = mysql_fetch_array($result1){ $sum+=$row1[LINE_PRICE]; } $query = "insert into INVOICETOTALS(INV_NUMBER,Total) values($inv,$sum)"; mysql_query($query); }

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.