0
SELECT
ratings.programme_id,
programme.programme_name,
ratings.ratings
FROM
programme
INNER JOIN ratings ON ratings.programme_id = programme.programme_id

I have two tables a and b

a has id and name and b has id and ratings. There can be multiple ratings for each id in a. I want to calculate the sum of ratings for each id.

for example entry john has received ratings of 1,3 and 5 and I would like to get the sum which would be 9

1 Answer 1

1

To answer this, I did the following.

mysql> CREATE TABLE myid(id INT, myname VARCHAR(10));
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO myid VALUES (1, 'Paul');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myid VALUES (2, 'Peter');
Query OK, 1 row affected (0.07 sec)

mysql> CREATE TABLE myrating(ratename VARCHAR(10), therating INT);
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO myrating VALUES('Paul', 3);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO myrating VALUES('Paul', 4);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO myrating VALUES('Peter', 6);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myrating VALUES('Peter', 456);
Query OK, 1 row affected (0.06 sec)

And then performed the query

SELECT myid.myname, SUM(myrating.therating) AS The_Rating 
FROM myid inner 
JOIN myrating 
ON myrating.ratename = myid.myname 
GROUP BY myid.myname;

Obtaining the result:

+--------+------------+
| myname | The_Rating |
+--------+------------+
| Paul   |          7 |
| Peter  |        462 |
+--------+------------+
2 rows in set (0.04 sec)

Does this satisfy your requirements?

3
  • thank you I had to adopt it to my database structure but it worked perfectly thanks
    – Ali Shaikh
    Oct 26, 2015 at 10:47
  • Thanks for the correct vote - you can make the query a little more elegant by doing something like the following "SELECT myid.myname, SUM(myrating.therating) AS Rating_Sum <rest of query identical...>, i.e. you can give your own alias to field name results using the AS keyword.
    – Vérace
    Oct 26, 2015 at 13:19
  • I did the formatting and demonstrated the AS word.
    – Vérace
    Oct 26, 2015 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.