Mysql - PHP - View Sum Column

Question

How i can view sum for the column (Collections) where (userid = $userid) ?

Example : I need to view the result for user id = 2, Collections = 200 + 330 = 530, I need to view this result (530)

My Table

------------------------------
| id | user_id | Collections |
------------------------------
| 1  |    2    |     200     |
------------------------------
| 2  |    2    |     330     |
------------------------------
| 3  |    7    |     120     |
------------------------------
| 4  |    8    |     760     |
------------------------------
| 5  |    9    |     200     |
------------------------------
| 6  |    9    |     100     |
------------------------------

My Code

<?php

$user_id = get_current_user_id();

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$query = "SELECT SUM(Collections) FROM invoices where user_id = $user_id"; 

$result = mysql_query($query) or die(mysql_error());



// Print out result

?>

I am a beginner in php & mysql

Abhishekh Gupta · Accepted Answer · 2015-05-23 06:28:57Z

2

You can try this:

SELECT SUM(i.Collections) AS totalCollection
FROM invoices AS i
WHERE i.user_id = '$user_id'
GROUP BY i.user_id

answered May 23, 2015 at 6:28

Abhishekh Gupta

6,2464 gold badges21 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abhishekh Gupta Over a year ago

@BakirOdeh Note:- Use GROUP BY in your query otherwise you will end up with some trouble when where clause will be removed.

Hitesh Vala Ahir · Accepted Answer · 2015-05-23 06:31:13Z

0

try this

SELECT SUM(Collections) AS totalvalue FROM invoices where user_id = '$user_id'

print result totalvalue

answered May 23, 2015 at 6:31

Hitesh Vala Ahir

8032 gold badges14 silver badges27 bronze badges

Comments

Josua M C · Accepted Answer · 2015-05-23 06:37:44Z

First

Check your type of collection column. Is it String / Int / Date? It should be number (Int, double, float)

Second

Your query is already correct. but the column name you should change it to result

SELECT SUM(Collections) as result FROM invoices where user_id = $user_id

and then check your function, from connection you are using mysqli but on your query you are using mysql this one should be changed to be same function.

Solution

<?php

$user_id = get_current_user_id();

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "dbname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$query = "SELECT SUM(Collections) FROM invoices where user_id = $user_id"; 

$result = mysqli_query($link, $query) or die('query error');

print_r($result);
// Print out result

?>

Collectives™ on Stack Overflow

Mysql - PHP - View Sum Column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related