0

I have a HTML table populated by a query:

         $sql = "SELECT user_id , pag_title , SUM(pag_views) FROM views WHERE user_id = '". $id ."' GROUP BY pag_title";

The output of the query can be (p.e.):

title 1 35
title 2 25 . . .

How can i count the 35 + 25 + ... ? I want to have a total sum.

Shub
  • 2,686
  • 17
  • 26
Brecht S
  • 115
  • 1
  • 2
  • 9
  • Have you tried to use something like $sql = "SELECT pag_title , SUM(pag_views) FROM views WHERE user_id = '". $id ."' GROUP BY pag_title, page_views"; . See here further infos http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns – pbaldauf Nov 02 '14 at 09:59
  • You want to calculate the sum in SQL or with javascript? If in Javascript, how does the output look? – Chief Wiggum Nov 02 '14 at 10:00
  • No it's pure PHP, MYSQL. No Javascript here. – Brecht S Nov 02 '14 at 10:01
  • Did you try SELECT pag_title , SUM(pag_views) FROM views WHERE user_id = '". $id ."' GROUP BY pag_title? – SMA Nov 02 '14 at 10:02
  • Yes I tried that but if i use the $num_rows = mysql_num_rows($res) then i have the sum of the pag_title but not the pag_views. – Brecht S Nov 02 '14 at 10:06

3 Answers3

0

Use: 

$sql = "SELECT sum(view) from(SELECT user_id , pag_title , SUM(pag_views) as view FROM views WHERE user_id = '". $id ."' GROUP BY pag_title) as a";
kiro
  • 111
  • 1
  • 4
  • this is really a silly thing. You get the same result with the query suggested by JamesBlond and your query is far slower than that one. – mgaido Nov 02 '14 at 10:10
0

That should do the trick. It selects the sum of all the views of a specific user:

$sql = "SELECT SUM(pag_views) FROM views WHERE user_id = '". $id ."'";

UPDATE

Assuming you use PHP with mySQL, something like this should work:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT SUM(pag_views) FROM views WHERE user_id = '". $id ."'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "views: " . $row[0]. "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>
Chief Wiggum
  • 2,784
  • 2
  • 31
  • 44
-1

What about having a PHP variable (called $sum for instance) and putting there your sum while iterating over the rows?

In alternative, you can put WITH ROLLUP at the end of you GROUP BY, so that you have an additional row in your result set with the sum you need.

mgaido
  • 2,987
  • 3
  • 17
  • 39
  • Can you explain what you mean? I don't understand? – Brecht S Nov 02 '14 at 10:02
  • I guess you loop over `msqli_fetch_assoc` or something similar to print the result. So, when you do that, you can also sum the value you are displaying for pages to a `$sum` variable (initialized to 0). In that way, at the end you have the value you need. – mgaido Nov 02 '14 at 10:07
  • Yes. It's working. I use $tot_pag_views = $row['SUM(pag_views)']; in my msqli_fetch_assoc. Thanks for helping me. – Brecht S Nov 02 '14 at 10:20