'SQL getting SUM of column [duplicate]

SELECT SUM(total_cost)
FROM purchase;

How do I make the value of SUM(total_cost) to a variable? If SUM(total_cost) = 300, how do I assign 300 to $result for example? I'm trying to do this in coldfusion, but php will work too.

phpsqlvariablessumcoldfusion


Solution 1:[1]

You can completely handle it in the MySQL query:

SELECT SUM(column_name) FROM table_name;

Using PDO (mysql_query is deprecated)

$stmt = $handler->prepare('SELECT SUM(value) AS value_sum FROM codes');
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);
$sum = $row['value_sum'];

Or using mysqli:

$result = mysqli_query($conn, 'SELECT SUM(value) AS value_sum FROM codes'); 
$row = mysqli_fetch_assoc($result); 
$sum = $row['value_sum'];

Solution 2:[2]

For mysqli

$data   = mysqli_query($conn, 'SELECT SUM(value) AS value_sum FROM codes'); 
$row    = mysqli_fetch_assoc($data); 
$result = $row['value_sum'];

Now u can get a Sum of value in $result

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Harunur Rashid
Solution 2 Anand A

Related Questions