CODEWITHSUNDEEP
mysqlsqlsubquerysum
penner
penner

Reputation: 2737

Sum one table where columns match another table and join the results

I have been trying to figure this out for a while now and decided I'm not going to figure it out any time soon! Thanks for the help in advance. Love this place.

I have 2 tables

date:

date_id, user_id, cost, title, description

date_rating:

date_id, user_id, rating

I have multiple entries with the same date_id but different user_id's. In other words multiple ratings of the same date. The ratings are either 1 or -1. I want to add all the ratings together with the same date_id and then join that to my DATE table where date_id matches. So that I have a column called rating on the table DATE. This way I can sort by rating. Sounds easy but it has proven to be not so easy. I guess DBA is not my thing!

edit: MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

Upvotes: 0

Views: 5000

Answers (3)

user756519
user756519

Reputation:

You have to use GROUP BY to sum the ratings. I have ignored the cost column from your table. And also, the user_id column. It seems that only date_id is relevant between these two tables. Below query was written and tested in SQL Server 2012 but I think that the SELECT statement should still work with MySQL with minor changes.

Script:

CREATE TABLE dbo.[date] 
(       
        [date_id]       INT             NOT NULL IDENTITY
    ,   [title]         VARCHAR(100)    NOT NULL
    ,   [description]   VARCHAR(100)    NOT NULL
);

CREATE TABLE dbo.[date_rating]
(       
        [date_id]       INT             NOT NULL
    ,   [user_id]       INT             NOT NULL
    ,   [rating]        INT             NOT NULL
);

INSERT INTO  dbo.[date] ([title], [description]) VALUES
    ('title 1', 'description 1'),
    ('title 2', 'description 2'),
    ('title 3', 'description 3');

INSERT INTO  dbo.[date_rating] ([date_id], [user_id], [rating]) VALUES
    (1, 100, 1),
    (1, 200, -1),
    (2, 200, -1),
    (1, 200, 1),
    (3, 200, 1),
    (1, 200, -1);

SELECT      d.[date_id]
        ,   d.[title]
        ,   d.[description]
        ,   SUM([rating]) AS ratings
FROM        dbo.[date]          d
INNER JOIN  dbo.[date_rating]   dr
ON          d.date_id           = dr.date_id
GROUP BY    d.[date_id]
        ,   d.[title]
        ,   d.[description];

Output:

date_id title   description    ratings
------- ------- -------------  -------
1       title 1 description 1    0
2       title 2 description 2   -1
3       title 3 description 3    1

Upvotes: 0

eggyal
eggyal

Reputation: 125925

You can use GROUP BY:

SELECT date.*, SUM(date_rating.rating) AS Rating
FROM date JOIN date_rating USING (date_id)
GROUP BY date_id;

Upvotes: 1

marc_s
marc_s

Reputation: 754953

Can't you just use something like this? I'm no expert at MySQL, mind you (I'm more a SQL Server guy):

SELECT
     date_id, user_id, cost, title, description,
     (SELECT SUM(rating) FROM date_rating dr WHERE dr.date_id = d.date_id) AS 'Rating'
FROM 
     date d

Just a correlated subquery to get the sum of ratings for each date specified by it's date_id ?

Upvotes: 6

Related Questions