Summing Multiple Rows with MySQL and outputting to table via PHP

Question

I have the following table:

Date	Product Number	Quantity	Amount
1-1-2022	308306	5	$157.70
1-3-2022	308309	10	$315.40
1-3-2022	401190	1	$13.48
1-4-2022	401190	5	$67.40

I'm looking to combine the rows by product number and count the quantity and sum the total amount.

Here is my query, which I think does what I want based on looking at examples on this site:

$query = "SELECT * FROM orders";
$result = mysqli_query($dbc, $query);

while($row = mysqli_fetch_array($result, MYSQLI_NUM))
{
    $prodnum = $row[1];
                
    $query2 = "SELECT * FROM products WHERE product_number = '$prodnum'";
    $result2 = mysqli_query($dbc, $query2);
    $row2 = mysqli_fetch_array($result2, MYSQLI_NUM);

    $query3 = "SELECT product_number, COUNT(*), SUM(total_amount) FROM orders GROUP BY product_number";
    $result3 = mysqli_query($dbc, $query3);
    $row3 = mysqli_fetch_array($result3, MYSQLI_NUM);
    
    $counter = $counter + 1;
    
    echo "
    <tr>
        <td> $row[1] </td>
        <td> $row2[2] </td>
        <td> $row3[2] </td>
        <td> $row3[2] </td>
    </tr>
    ";

}

I would expect the following to be output:

Prod Num	Description	Count	Total Amount
308306	BROWNIE, CHOC CRML SALTD	15	473.10
401190	MUSTARD, YLW SQZ BTL SHLF	6	80.88

The problem is that all I get is the following:

Prod Num	Description	Count	Total Amount
308306	BROWNIE, CHOC CRML SALTD	1	24.05
401190	MUSTARD, YLW SQZ BTL SHLF	1	24.05
760785	SPICE, PPR BLK GRND JUG REST	1	24.05

Any help is appreciated.

Thank you.

And use `JOIN` to connect the `orders` and `products` tables, instead of doing separate queries in the loop. — Barmar, Mar 24 '22 at 18:49
These are both basic SQL operations. You need to study a tutorial. — Barmar, Mar 24 '22 at 18:49
`SELECT product_number, SUM(quantity), SUM(total_amount) FROM orders GROUP BY product_number` — dokgu, Mar 24 '22 at 18:54
Thank you for the feedback. I'm new to all this and have been studying but the problem I'm having is the output. I've used the SUM and GROUP BY but the exported values don't line up. — JohnnyO, Mar 24 '22 at 19:14
Determine where the problem is first: php or sql. Run the query directly in MySQL. If that returns the expected results, the problem is the php code. Also 1) Take note of [Barmar's comment](https://stackoverflow.com/questions/71607890/summing-multiple-rows-with-mysql-and-outputting-to-table-via-php#comment126557531_71607890) about using a JOIN. Aside from being unnecessary, querying within a loop is *very* inefficient. 2) Look into [protecting the code from sql injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — SOS, Mar 24 '22 at 19:29

score 0 · Answer 1 · edited Mar 25 '22 at 02:28

I don't know your DB structure but I assume your table has these 3 fields. check the below MySql Query. I hope it will resolve your expectations.

select pdtnum, sum(qty) as qty, ROUND(sum(amt), 2) as amt
from   product 
group by pdtnum;

For Mysql table reference

-- create
CREATE TABLE product (
  id INTEGER PRIMARY KEY,
  pdtnum INTEGER NOT NULL,
  qty INTEGER NOT NULL,
  amt float NOT NULL
);

-- insert
INSERT INTO product VALUES (1, 308306, 5, 157.70);
INSERT INTO product VALUES (2, 308306, 10, 315.40);
INSERT INTO product VALUES (3, 401190, 1, 13.48);
INSERT INTO product VALUES (4, 401190, 5, 67.40);

-- fetch 
-- SELECT * FROM product;-- WHERE pdtnum = '308306';

select pdtnum, sum(qty) as qty, ROUND(sum(amt), 2) as amt from product group by pdtnum;

Output:

pdtnum	qty	amt
308306	15	473.1
401190	6	80.88

if you want print amount with $ then try the below

select pdtnum
       , sum(qty) as qty
       , concat("$ ",ROUND(sum(amt), 2)) as amt 
from  product 
group by pdtnum;

Output:

pdtnum	qty	amt
308306	15	$ 473.1
401190	6	$ 80.88

Summing Multiple Rows with MySQL and outputting to table via PHP

1 Answers1