Sum, Grouping And Join Trouble

[MMDE]

Heya

 

I've been having a hard time getting my mysql query to do as I want.

 

 

SELECT m.team, SUM(pc.points) AS total_points
FROM members AS m
JOIN pointscount AS pc ON pc.tournamentid = m.tournament
JOIN persons AS p ON p.pid = m.person
WHERE p.secret = 1 AND m.tournament = 5 AND pc.day < 8
GROUP BY m.team

 

SELECT m.team, SUM(pc.points) AS total_points
FROM members AS m
JOIN pointscount AS pc ON pc.tournamentid = m.tournament
JOIN persons AS p ON p.pid = m.person
WHERE p.secret = 0 AND m.tournament = 5
GROUP BY m.team

 

Yeah, this is what I've been writing.

 

The table `members` contain information about what kind of tournament and team a certain person is in.

The table `pointscount` contain information about how many points a person has got each day of a certain tournament, one row per person per day per tournament. So yes, they are grouped as being UNIQUE together.

The table `persons` contains the id of a person, but it also contains a field called secret, which if is set to true is supposed to be used to keep the points they've obtained secret after a certain date.

 

What I want the query to do:

Get the team name and get the total of points this team has earned, but this with a little twist. I want the second query to only sum the amount of points earned by the team where it's members have had their secret field in the person table set to false. The first one wants to do almost the opposite, but instead of getting everyone who has set their secret field in the person table to true, it wants to only get the point they've earned before the 8th day. o.o'

 

Hopefully this makes any sense.

 

Thanks in advance.

[Barand]

Can we see your table structures and relationships?

 

edit: PS and, if possible, sample data

Edited October 4, 2012 by Barand

[MMDE]

Can we see your table structures and relationships?

 

edit: PS and, if possible, sample data

 

CREATE TABLE persons(
pid INT NOT NULL AUTO_INCREMENT,
username VARCHAR(16) NOT NULL,
secret BOOLEAN DEFAULT 0,
PRIMARY KEY(pid)
);

CREATE TABLE members(
tournament SMALLINT NOT NULL,
person INT NOT NULL,
team INT NOT NULL,
PRIMARY KEY(tournament, person, team)
);

CREATE TABLE pointscount(
tournamentid SMALLINT NOT NULL,
personid INT NOT NULL,
day INT NOT NULL,
points INT DEFAULT 0,
PRIMARY KEY(tournamentid, personid, day)
);

INSERT INTO persons (username, secret) VALUES ('MMDE1', 1);
INSERT INTO persons (username, secret) VALUES ('MMDE2', 0);
INSERT INTO persons (username, secret) VALUES ('MMDE3', 1);
INSERT INTO persons (username, secret) VALUES ('MMDE4', 0);
INSERT INTO persons (username, secret) VALUES ('MMDE5', 1);
INSERT INTO persons (username, secret) VALUES ('MMDE6', 0);
INSERT INTO persons (username, secret) VALUES ('MMDE7', 1);
INSERT INTO persons (username, secret) VALUES ('MMDE8', 0);

INSERT INTO members (tournament, person, team) VALUES (1, 1, 1);
INSERT INTO members (tournament, person, team) VALUES (1, 2, 1);
INSERT INTO members (tournament, person, team) VALUES (1, 3, 1);
INSERT INTO members (tournament, person, team) VALUES (1, 4, 1);
INSERT INTO members (tournament, person, team) VALUES (1, 5, 2);
INSERT INTO members (tournament, person, team) VALUES (1, 6, 2);
INSERT INTO members (tournament, person, team) VALUES (1, 7, 2);
INSERT INTO members (tournament, person, team) VALUES (1, 8, 2);

INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 1, 7, 10);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 1, 8, 11);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 2, 7, 12);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 2, 8, 13);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 3, 7, 14);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 3, 8, 15);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 4, 7, 16);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 4, 8, 17);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 5, 7, 18);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 5, 8, 19);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 6, 7, 20);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 6, 8, 21);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 7, 7, 22);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 7, 8, 23);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 8, 7, 24);
INSERT INTO pointscount (tournamentid, personid, day, points) VALUES (1, 8, 8, 25);

 

I hope it's all correct syntax, as I wrote it all freehand.

 

Why doesn't it show tabs in the code tag anymore? o.O

Edited October 4, 2012 by MMDE

[MMDE]

What I want the results to be:

 

First query result:

row 1: team = 1, total_points = 28

row 2: team = 2, total_points = 44

 

Second query result:

row 1: team = 1, total_points = 50

row 2: team = 2, total_points = 82

[Barand]

Joining on the person ids

 

SELECT m.team, SUM(pc.points) AS total_points

FROM members AS m

JOIN persons AS p ON p.pid = m.person

JOIN pointscount AS pc ON pc.personid = p.pid

WHERE p.secret = 1 AND m.tournament = 1 AND pc.day < 8

GROUP BY m.team;

 

SELECT m.team, SUM(pc.points) AS total_points

FROM members AS m

JOIN persons AS p ON p.pid = m.person

JOIN pointscount AS pc ON pc.personid = p.pid

WHERE p.secret = 0 AND m.tournament = 1

GROUP BY m.team;

[MMDE]

Great, works as intended.

I kinda see what I did wrong now.

 

Thanks a lot.