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

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 '15 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. Oct 26 '15 at 13:19
  • I did the formatting and demonstrated the AS word. Oct 26 '15 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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