CODEWITHSUNDEEP
mysql
battlenub
battlenub

Reputation: 187

Mysql Group by counting issue

Database structure

Table 'applicants'

  id       org_id      team_id
   1            1            1

Table 'teams'

  id        name
   1        Test

Table 'teams_members'

  id      team_id       user_id
   1            1             1
   2            1             2

Table 'users_playeraccounts'

  id      user_id        summoner_id           rank_solo
   1            1                  1                   5
   2            1                  2                   8  
   3            2                  3                   7



select sum(rank_solo) as rank_sum,
       max(rank_solo) as  highest_rank,
       count(tt.id) as members,
       t.name,
       o.team_id
           from applicants o 
           join teams t on o.team_id = t.id
           join teams_members tt on t.id = tt.team_id       
           join users_playeraccounts p on tt.user_id = p.user_id
           where org_id = :org
           group by team_id

This offcourse gives me a result like

    rank_sum      highest_rank      members    name     team_id

    20                 8            3          Test           1

Is there a way for me to get both the count of members with their playeraccounts aka

If 1 user has 2 it'll be 2

And also a way for me to keep it as 1 so it literally just counts the rows found in teams_members neglecting the entries in users_playeraccounts?

I want to receive both 2 and 3 as a result of my query.

Upvotes: 0

Views: 24

Answers (1)

jbafford
jbafford

Reputation: 5668

You want to count the distinct number of entries in tt.id, so you can do that like this:

SELECT ... COUNT(DISTINCT tt.id) AS distinct_members ...

Rather than giving you a count of every row that has a non-null tt.id, you'll get a count of the number of unique values.

Upvotes: 1

Related Questions