Given the following two tables:
- users (user_id, username, location, email)
- votes (user_id, value, date, ip)
How can I perform a MySQL query to return all the users with total number of votes and sum of votes for each user?
Thank you.
Add a comment
|
2 Answers
select u.user_id,
(select count(user_id)
from votes v
where v.user_id = u.user_id) as num_votes,
(select sum(value)
from votes v
where v.user_id = u.user_id) as sum_votes
from users u
# if you want: order by ...
EDIT: The subselects are not the most elegant solution, and not really necessary. So here's an outer join version:
select u.user_id, count(v.user_id) as num_votes, sum(v.value) as sum_votes
from users u
left outer join votes v
on u.user_id = v.user_id
group by u.user_id
This works for me:
create table user
(
id int not null auto_increment,
name varchar(80) not null,
location varchar(80) default '',
email varchar(80) default '',
primary key(id)
);
create table vote
(
id int not null auto_increment,
count int not null,
election_date date,
user_id int,
primary key(id),
foreign key(user_id) references user(id)
);
select
user.name, user.email, sum(vote.count)
from user
left join
vote
on user.id = vote.user_id
group by (user_id)
Comments
