SQL SUM and COUNT from different tables

Question

Tables:

 UserReward:
        UserRewardID PK
        RewardID FK
        UserID FK

    UserBadge:
        UserBadgeID PK
        BadgeID FK
        UserID FK

    UserScore:
        UserScoreID PK
        UserID FK
        LeaderboardID FK

I need to know the sum of score, the count of userbadge and the count of userReward. I tried this but values are not right:

Select 
  u.username,
  sum(us.score) as Soma_Score, 
  count(ur.userId) as Numero_de_rewards, 
  count(ub.userId) as Numero_de_crachas
from [user] u 
join userscore us on u.userId = us.userID
join userbadge ub on ub.userid = u.userid
join userreward ur on ur.userid= u.userid
group by u.username

Sample data, what you're getting, and what you expect would help. Check out the importance of a Minimal, Complete, Verifiable Example. After that, Start Here to edit your question as needed. — Eric Brandt
– Eric Brandt, Commented Oct 18, 2018 at 18:53

Corion · Accepted Answer · 2018-10-18 18:52:23Z

3

Have you looked at the rows before aggregating them? Your JOINs are duplicating many rows.

The best approach is to join the rows after aggregation:

with score(userid, score) as (
Select userid
     , sum(us.score) as Soma_Score
  from userscore us
  group by userid
), rewards (userid, rewards) as (
select userid
     , count(ur.userId) as Numero_de_rewards
  from  userreward ur
  group by userid
), crachas (userid, crachas) as
select userid
    , count(userId)
  from userbadge
  group by userid
)
select
     u.userid
   , score.score
   , rewards.rewards
   , crachas.crachas
from user u
left join score on u.userid=score.userid
left join rewards on u.userid=rewards.userid
left join crachas on u.userid=crachas.userid

answered Oct 18, 2018 at 18:52

Corion

3,9151 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lpacheco · Accepted Answer · 2018-10-18 18:54:37Z

2

Try:

SELECT
    u.username,
    (SELECT SUM(us.score) FROM userscore us WHERE us.userid = u.userid) as Soma_Score,
    (SELECT COUNT(ur.userId) FROM userreward ur WHERE ur.userid = u.userid) as numero_de_rewards, 
    (SELECT COUNT(ub.userId) FROM userbadge ub WHERE ub.userid = u.userid) as numero_de_crachas
FROM [user] u

answered Oct 18, 2018 at 18:54

lpacheco

1,0361 gold badge16 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL SUM and COUNT from different tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related