SUM data based on a Group By statement for another table

Question

Hey guys,

I am trying to create a query that allows me to get the sum of a total stored in one table based on values in another table.

Specifically, I have one table called 'winning_bids', that I want to join with another table, called 'objects'. 'winning_bids' contains a User ID, and an Object ID (primary key of 'objects' table). The 'objects' table contains an Object ID, and the value of the object. I want to sum the value from the 'objects' table for each user, grouped by the User ID from the 'winning_bids' table.

I tried something like this, but it does not work:

SELECT SUM(o.value) AS total, w.uid
  FROM winning_bids w 
  LEFT JOIN objects o ON (o.id = w.oid)
 GROUP BY w.uid

This statement merely returns all of the User IDs, but with the total for only the first User ID in each row.

Any help would be appreciated, thanks.

Answer 1

It works fine for me.

Here is what I did to test your query:

CREATE TABLE winning_bids (uid INT NOT NULL, oid INT NOT NULL);
INSERT INTO winning_bids (uid, oid) VALUES
(1, 1),
(1, 2),
(2, 3);

CREATE TABLE objects (id INT NOT NULL, value INT NOT NULL);
INSERT INTO objects (id, value) VALUES
(1, 1),
(2, 20),
(3, 300);

SELECT SUM(o.value) AS total, w.uid
FROM winning_bids w
LEFT JOIN objects o ON (o.id = w.oid)
GROUP BY w.uid;

Result:

total  uid
21     1
300    2

If you still think it doesn't work can you please post example input data that gives the wrong result when you run your query, and also specify what you believe that the correct result should be.