Retrieving the sum of two independent columns - using nested select statements

Question

Essentially I'm attempting to display the ID, Title, total quantity, and total sales price of books within a book store. Currently I'm able to display the ID, Title, and Total Quantity - OR the ID, Title, and Total sales price, I can not however figure out how to retrieve both the total quantity and sales price at the same time from this table.

This Query successfully gives me the ID, Title and Quantity:

SELECT ID,Title,(SELECT Sum(Quantity) from Sale where BookId = Book.id ) as Quantity from Book;

This Query successfully gives me the ID, Title and Sales Price:

SELECT ID,Title,(SELECT Sum(UnitPrice) from Sale where BookId = Book.id ) as Price from Book;

So how could I retrieve both the Quantity and Sales Price in our query? Both values are within the same table, but there are two tables being used here.

Heres my current HTML that im using to loop through and display the results of the query:

<!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>

Thanks!

Just so you know, you are executing your query twice. `$result = $conn-> query($sql); if ($result = $conn->query($sql)) {` There is also an easier way to [iterate the result set](https://stackoverflow.com/a/66775416/2943403). You should not mix object oriented and procedural `mysqli_` syntax. Go with object oriented syntax and use it consistently. — mickmackusa, Apr 07 '21 at 03:36

score 1 · Accepted Answer · answered Apr 04 '21 at 06:31

1

Good day, You can try this query

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;

answered Apr 04 '21 at 06:31

Ahmed Merghani

41
4

This answer is missing its educational explanation. – mickmackusa Apr 07 '21 at 03:35

score 0 · Answer 2 · answered Apr 04 '21 at 11:42

0

Don't use two correlated subqueries! Use aggregation. One method is:

select b.id, b.title, Sum(s.Quantity) as quantity, sum(s.UnitPrice) as price
from Book b left join
     Sales s
     on s.bookid = b.bookid
group by b.id, b.title;

Note: The sum of UnitPrice on different sales with different quantities doesn't seem useful to me. More likely, you want the total price:

select b.id, b.title, Sum(s.Quantity) as quantity, 
       sum(s.UnitPrice * s.Quantity) as price
from Book b left join
     Sales s
     on s.bookid = b.bookid
group by b.id, b.title;

answered Apr 04 '21 at 11:42

Gordon Linoff

1,242,037
58
646
786

Hey there Gordon, Thanks for helping me! how could I implement this in my current HTML loop? I tried but I think I did it incorrectly. Thanks! – NightBeezy Apr 07 '21 at 01:11
@Night you should try very hard to implement Gordon's answer as it is using far better practice. Using subqueries in the SELECT clause is a bad idea. – mickmackusa Apr 07 '21 at 04:58

Retrieving the sum of two independent columns - using nested select statements

2 Answers2