CODEWITHSUNDEEP
phpmysql
Niels
Niels

Reputation: 43

mysql sum after group by

Cart table: 

itemid | amount 
------ | ------
1      | 2
2      | 4

Products table: 

itemid | Price
------ | ------
1      | 10
2      | 20

What I have tried so far: 

$cartPrice = 
"SELECT (SUM(products.Price) * SUM(cart.amount)) 
AS total_price 
FROM cart 
INNER JOIN products 
ON cart.itemid = products.itemid 
WHERE userid = '$IP' 
GROUP BY cart.itemid";

$Price = $mysqli->query($cartPrice);
$rowPrice = $Price->fetch_assoc();

echo "Total: ". $rowPrice['total_price'] ." ";

My MYSQL Query outputs the following: 

total_price |
----------- | 
20          | 
80          |

It will echo the last result. But I need the total of 80 + 20. How do I do this?

Result I get at the moment:

Total: 80

Result I want: 

Total: 100

Thanks

Upvotes: 2

Views: 318

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133360

A simple way is the use of an union with a second select without the group by

" SELECT (SUM(products.Price) * SUM(cart.amount))  AS total_price 
  FROM cart 
  INNER JOIN products 
  ON cart.itemid = products.itemid 
  WHERE userid = '$IP' 
  GROUP BY cart.itemid
  UNION 
  SELECT (SUM(products.Price) * SUM(cart.amount))  
  FROM cart 
  INNER JOIN products 
  ON cart.itemid = products.itemid 
  WHERE userid = '$IP' ";

the firts select return the main rows and the second the final total

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You don't have to GROUP BY since you want only one row returned:

SELECT SUM(products.Price* cart.amount) AS total_price 
FROM cart 
INNER JOIN products 
ON cart.itemid = products.itemid 
WHERE userid = '$IP'

Upvotes: 2

Felippe Duarte
Felippe Duarte

Reputation: 15131

You can SUM the result of a subquery, like this:

SELECT SUM(total_price) as total_price FROM
(
    SELECT (SUM(products.Price) * SUM(cart.amount))  AS total_price 
    FROM cart 
    INNER JOIN products 
    ON cart.itemid = products.itemid 
    WHERE userid = '$IP' 
    GROUP BY cart.itemid
) total_prices

Upvotes: 1

Related Questions