CODEWITHSUNDEEP
sql
André
André

Reputation: 37

SQL SUM and COUNT from different tables

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

Upvotes: 0

Views: 56

Answers (2)

lpacheco
lpacheco

Reputation: 1026

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

Upvotes: 2

Corion
Corion

Reputation: 3925

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

Upvotes: 3

Related Questions