What’s the best way to count the number of references based on conditions in both “home” and “away” tables?

I’m working on a project, and using Knex for it, (though if you give me raw SQL I’m pretty sure I can convert that as well.)

So, here’s the sitch.

I have tables ‘group’ and ‘user_group’.

table 'group' 
  id: integer
  name: string
  is_public: boolean
table 'user_group'
  group_id: ref(group.id)
  user_id: ref(user.id) // don't need user table info for this
  role: string 

What I’d like to do is get a list of all groups

• where is_public is TRUE
• returning the group.name and group.id
• and a count of how many members the group has.
• sorted from most members to least

So an output would look like this:

[
  {name: "Clowns", memberCount: 20, id: 3},
  {name: "Puppets", memberCount: 12, id: 36},
  {name: "Jugglers", memberCount: 5, id: 12},
  ...
]

Here’s what I’ve got so far:

  SELECT COUNT('*') AS memberCount, group.name AS name, group.id AS id
  FROM group
  INNER_JOIN user_group on group.id = user_group.group_id
  WHERE group.is_public = true
  AND WHERE user_group.role = "MEMBER"
  ORDER BY memberCount ASC`

Am I on the right track?

Answer

I would approach this as:

SELECT g.name, g.id, COUNT(ug.role) AS memberCount
FROM group g LEFT JOIN
     user_group ug
     ON ug.group_id = g.id AND
        ug.role = 'MEMBER'
WHERE g.is_public
GROUP BY g.name, g.id
ORDER BY memberCount DESC;

How is this structured? First, this is using a LEFT JOIN because you want all groups, presumably even those with no members. Because of the LEFT JOIN, the condition on the role is in the ON clause rather than the WHERE clause.

Some other notes:

• Use single quotes for string and date constants. This is the SQL standards.
• Aggregate by all the keys in the SELECT.
• To get results from highest to lowest, use a DESC ORDER BY.
• There is no need to specify = true for a boolean, although there is no issue either.