SQL finding total sale of items of varying quantity and price

thanks for trying to help me! Essentially I’m trying to create an HTML table that displays a variety of items from a table in SQL – regarding books and book sales. Basically I need to find the total dollar amount sales each book has, and these books can be the same book but vary in price, and each sale varies in quantity. As of now, I’ve successfully created a loop in PHP to display such a table, and it looks like this: enter image description here

Unfortunately, the total sales price calculation is incorrect, and I’m attempting to figure out why – I suspect its something to do with my query.

Here is the table in question: enter image description here

As you can see in the table, the same book can appear a variety of times, and have differing quantities and unit prices. Some of my calculations are correct, others however are incorrect. Book ID 103 for example is incorrect.

Here’s my HTML Code and thanks again for trying tp help me sort this out!:

<!DOCTYPE html>
<html lang="en">
    <!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<link rel="stylesheet" href="new.css">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>

    <nav class="navbar navbar-default navbar navbar-inverse">
        <div class="container">
        <h2> <a class="head" href="base.php" style="text-decoration: none;">Database Project</a> <a class="btn btn-danger btn-sm main" href="login.php" role="button">Sign Out</a></h2>
        </div>
    </nav>

    <div class="container">
    <div class="jumbotron">
        <h1>Book Report:</h1><br>
        <table>
            <tr>
                <th>ID</th>
                <th>Title</th>
                <th>Quantity</th>
                <th>Total Sale Price</th>
    
            </tr>
            <?php
            $conn = mysqli_connect("localhost","root","","Zion");
            if ($conn-> connect_error){
                die("Connection Failed:". $conn-> connect_error);
            }
            $sql = "SELECT ID,Title,(SELECT Sum(Quantity) from Sale where BookId = Book.id ) as Quantity,(SELECT Sum(UnitPrice) from Sale where BookId = Book.id ) as Price from Book";
            $result = $conn-> query($sql);
            if ($result = $conn->query($sql)) {
                while($row = $result->fetch_assoc()) {
                    echo "<tr><td>". $row["ID"] . "</td><td>". $row["Title"]."</td><td>". $row["Quantity"]. "</td><td>". "$".$row["Price"]. "</td></tr>";
                }
                echo"</table>";
            }
            $conn-> close()
            ?>
            <!-- Generate a report that for each book, displays the book id, the book title, 
            the quantity and the total sales price, sorts by the book id in increasing order; -->

</div>

</body>
</html>

Answer

The following sql query using a join will help you to achieve your goal. The query retrieves for each Book (grouping by Book.Id and Book.Title), the total sold stored in Quantity and the Price at each sale, determined by multiplying the amount of a particular book by the unit price of each book at that time.

SELECT 
    Book.ID,
    Book.Title,
    SUM(Sale.Quantity) as Quantity,
    SUM(Sale.UnitPrice*Sale.Quantity) as Price
FROM
    Sale 
INNER JOIN
    Book on Book.Id = Sale.BookId
GROUP BY
    Book.ID, Book.Title

I have used the same column names, so this should work as is in your php code eg:

$sql="SELECT Book.ID, Book.Title, SUM(Sale.Quantity) as Quantity, SUM(Sale.UnitPrice*Sale.Quantity) as Price FROM Sale INNER JOIN Book on Book.Id = Sale.BookId GROUP BY Book.ID, Book.Title";