Reputation: 654
I've spent the last 8 hours trying to get this SQL query to work and I can't get it done. I am not very experienced, but am trying to get better at it.
I've got 2 tables:
Table giveaway
Columns:
id | userid | created | closed | winnerid | cancelled
1 | 7962290564 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290566 | NULL
2 | 7962290565 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290567 | NULL
3 | 7962290566 | 2014-07-26 20:32:22 | 2014-07-26 20:32:22 | 7962290567 | NULL
4 | 7962290566 | 2014-07-26 20:32:22 | 0000-00-00 00:00:00 | NULL | NULL
Table user
Columns:
id | steamid | joined | roleid | name
1 | 7962290564 | 2014-07-26 20:32:22 | 1 | User a
2 | 7962290565 | 2014-07-26 20:32:22 | 4 | User b
3 | 7962290566 | 2014-07-26 20:32:22 | 4 | User c
4 | 7962290567 | 2014-07-26 20:32:22 | 4 | User d
5 | 7962290568 | 2014-07-26 20:32:22 | 4 | User e
6 | 7962290569 | 2014-07-26 20:32:22 | 5 | User f
What I'm after is a single result that gives a count of how many times someone has his id in giveaway.userid and how many times someone has his id in giveaway.winnerid. Also it has a few WHERE elements to filter out unwanted results.
It's something like:
SELECT user.steamid, user.name, count(giveaway.userid) nrgiven, count(giveaway.winnerid) nrwon
FROM user
WHERE user.roleid BETWEEN 1 AND 4
AND giveaway.cancelled IS NULL
AND giveaway.closed IS NOT NULL
GROUP BY user.steamid
For these tables user.steamid = giveaway.userid
user.steamid = giveaway.winnerid
The result should be:
steamid | name | nrwon | nrgiven
7962290564 | User a | 0 | 1
7962290565 | User b | 0 | 1
7962290566 | User c | 1 | 2
7962290567 | User d | 2 | 0
7962290568 | User e | 0 | 0
Here are some examples of what I've tried, but none of them does a good counting.
SELECT user.steamid, user.name, count(giveaway.userid) nrgiven
FROM user
INNER JOIN giveaway
ON user.steamid = giveaway.userid
AND user.roleid BETWEEN 1 AND 4
AND giveaway.closed IS NOT NULL
AND giveaway.cancelled IS NULL
GROUP by user.steamid
ORDER BY nrgiven DESC;
---
SELECT user.roleid, user.steamid, user.name, a.nrgiven, b.nrwon
FROM user,
( SELECT count(user.steamid) nrgiven
FROM user
LEFT JOIN giveaway ON user.steamid = giveaway.userid
) a,
( SELECT count(user.steamid) nrwon
FROM user
LEFT JOIN giveaway ON user.steamid = giveaway.winnerid
) b;
---
SELECT user.steamid, user.name, a.nrwon
FROM user,
( SELECT count(giveaway.winnerid) nrwon
FROM giveaway
RIGHT JOIN user ON giveaway.winnerid = user.steamid
WHERE user.roleid BETWEEN 1 AND 4
AND giveaway.closed != 0
AND giveaway.winnerid IS NOT NULL
GROUP BY user.steamid
) a
GROUP BY user.steamid
ORDER BY steamid DESC;
Upvotes: 1
Views: 123
Reputation: 5756
I was able to get your expected result table from that dataset with the following query:
SELECT user.steamid, user.name, COALESCE(winners.nrwon,0) as nrwon, COALESCE(nrgiveaways.nrgiven,0) as nrgiven
FROM user
LEFT JOIN (SELECT winnerid, COUNT(*) as nrwon FROM giveaway WHERE winnerid IS NOT NULL GROUP BY winnerid) as winners on winners.winnerid = user.steamid
LEFT JOIN (SELECT userid, COUNT(*) as nrgiven FROM giveaway GROUP BY userid) as nrgiveaways on nrgiveaways.userid = user.steamid
WHERE roleid in (1,2,3,4)
The winners and giveaways are found in their own tables and then added to the user table using left joins.
Upvotes: 1