Add values from a column

[Fmdpa]

I am trying to think of a way to add values from a column. I considered adding the values in the loop unpacking the mysql resource in PHP, but is there a way I could do this all within SQL? For example, how could I get 9 from this, if it was a column:

+----+|  3   ||  2   ||  4   |+----+

[birbal]

do you mean taking a column from a row and insert it in another table?

[skaterdav85]

SQL aggregate functions are used for performing calculations on data. You'd use the SUM() function, so to get 9 you'd do:

SELECT SUM(fieldName) AS TheSumFROM tableName

[Fmdpa]

Thanks Dave! Now I've got another question. I've never really done any SQL joins, and I'm trying to see if they would work in my application. I have two tables. One is the comment table, and for now, let's make it look like this:

+---+------+| id |  post |+---+-------++---+------+| id | rating|+---+-------+

The ids tie the tables together. The first table contains the comments. The second contains the ratings for the comments. In the first table, there is one id per comment. In the second table, there may be numerous rows with the same ids (representing the comment). So I might have something like this:

+---+------+| id |  post |+---+-------+------+|  5 | Hello World! |+---+---------------+|  6 | Hello Again!  |+---+---------------++---+------+| id | rating|+---+------+| 5  |  -1	|| 5  |   1	|| 5  |   1	|| 5  |  -1	|| 6  |   1	|| 5  |  -1	|| 6  |   1	|| 6  |  -1	|| 5  |   1	|| 6  |   1	|+---+------+

I want the result to look like this:

+------+------+----------------------+|   id   | sum  |		   Post		   |+------+------+----------------------+|   5	|   0	|	  Hello World!	 |+------+------+----------------------+|   6	|   2	|	  Hello Again!	 |+------+------+----------------------+

I tried something like this:

SELECT a.id AS id, SUM(b.id) AS sum, a.post AS post, FROM comments AS a INNER JOIN comment_rating AS b

It came up with some crazy result. Using one query (I forgot what it was, but it made use of a join), I was able to select a comment, the id, and the sum all accurately, but it only gave me one result. How would I get the result like that shown in the previous codebox?

[skaterdav85]

Well for starters, your 2nd table doesn't even have a primary key. You need some sort of unique identifier for each row, and right now you have multiple rows with 5 as the id and 1 as the rating. Im not too sure if you're allowed to do this or not, but it probably is not the best practice to not have a PK in a table, otherwise your design is probably flawed.Hmmm this is a tricky query. I almost feel you need a stored procedure for this. Anyone else know if you can do this without a SP?

[justsomeguy]

Why are you using SUM on the id column, don't you want to sum the ratings instead of the IDs? You want to group by the ID, but sum the ratings.SELECT a.id AS id, a.post, SUM(b.rating) AS sum, FROM comments AS a INNER JOIN comment_rating AS b ON a.id=b.id GROUP BY b.id, a.post

[Fmdpa]

Oops! Yes, that's right. I made the mistake of summing up the values of the id multiple times. Your query works for the situation I showed. But, it works on all comments that are rated. If the comment does not have a rating in table #2, then the comment doesn't show up in the result list at all. How would I fix that?I fixed the problem of the PK. Thanks for mentioning that, Dave.

[justsomeguy]

You need to use a left or right join. If you do this:a LEFT JOIN bSince it is a "left" join, it takes every row from the "left" table, a, and joins each record with whatever records match from b. If b doesn't have any matching records then those fields contain null values.

[Fmdpa]

Ok, thanks! I think this is giving me a better understanding of JOINs.

[skaterdav85]

why would you group by post?

[justsomeguy]

For the same reason you group by ID. Generally, any fields that are not part of an aggregate function go into the group by clause.