CODEWITHSUNDEEP
phpmysql
Henry James H.
Henry James H.

Reputation: 1

How to sum and multiply mysql two rows

I have two products with same unique code (Database table):

+----------------------------------+-----+-------+
|               CODE               | QTY | Price |
+----------------------------------+-----+-------+
| 977b8213fd7s7d0129f4358a75ca967f | 143 | 11.83 |
| 977b8213fd7s7d0129f4358a75ca967f |  31 | 10.48 |
+----------------------------------+-----+-------+

I must sum and multiply QTY and Price to get a total price.

In above example, total price should be 2016.57 (11.83 × 143 + 10.48 × 31 = 2016.57).

I tried this but I get two totals (1691.69 and 2016.57):

$code = "977b8213fd7s7d0129f4358a75ca967f";
$ttl=0;

try{
    $database = new Connection();
    $db = $database->openConnection();
    $sql= "SELECT qty*rraate AS sum FROM carrrt WHERE code = :code";
    $qry = $db->prepare($sql);
    $qry -> bindParam(':code', $code, PDO::PARAM_INT);
    $qry -> execute();
    while ($qnow = $qry->fetch(PDO::FETCH_ASSOC)){
        $ttl += $qnow['sum'];
         echo $ttl;
    }   

} catch (PDOException $e) {
    echo "There is some problem in connection: " . $e->getMessage();
}

Why am I getting two totals and what I am doing wrong?

Upvotes: 0

Views: 243

Answers (2)

Joel
Joel

Reputation: 192

Try :

SELECT ROUND(SUM(rraate*qty),2) AS sum FROM carrrt WHERE code = :code";

Upvotes: 0

NathanFrasier
NathanFrasier

Reputation: 308

It sounds like you want to use an aggregate.

See if this fixes your problem

$sql= "SELECT SUM(qty*rraate) AS sum FROM carrrt WHERE code = :code";

The SUM() in the select I used is called an aggregate, which is the primary way to collect data from every row and combine it into some value.

Unless you use an aggregate, you will get a distinct answer for every row in your table. For more information on aggregates, you can look here

Upvotes: 1

Related Questions