Write a query to produce the number of invoices and the total purchase amounts b

Question

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. List the balances of customers who have made purchases during the current invoice cycle-that is, for the customers who appear in the INVOICE table. The results of this query are shown in Figure P7.37. Using the results of the query created in Problem 37, provide a summary of customer balance characteristics, as shown in Figure P7.38.

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 W
• Subjects

---

---

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