Figure out which group has the most training group

user_10469209 0
Viewed 12

I have a bunch of groups. Each group entry is owned by another group entry. For example, the soccer club group owns soccer training, soccer cardio squad, pro training and beginner training. I want to find out which group owns the most training type group. To determine which group belongs to each other, I have a function called groupOwnedBy() which returns the id the group which owns the group.

For example, if we want to check which group owns soccer training, we pass in 2, as that is the id for soccer training

SELECT * FROM groupOwnedBy(2);

will return the id of group, 1, which owns soccer training (meaning it is owned by soccer club)

groupOwnedBy
------------
1

SELECT * from groupOwnedBy(1); -- will return 1. soccer club is owned by soccer club

I want to print the name of the group which owns the most training type groups. In this case it should print soccer club.

CREATE TABLE groups (
  id integer primary key,
  type text NOT NULL,
  name text
);


INSERT INTO groups (id, type, name) VALUES
(1, 'owner', 'soccer club'),          -- owned by soccer club
(2, 'training', 'soccer training'),   -- owned by soccer club
(3, 'cardio', 'soccer cardio squad'), -- owned by soccer club
(4, 'training', 'pro training'),      -- owned by soccer club
(5, 'training', 'beginner training'), -- owned by soccer club
(6, 'cardio', 'Cricket cardio team'), -- owned by cricket club
(7, 'owner', 'Cricket club'),         -- owned by cricket club
(8, 'training', 'Cricket training'),  -- owned by cricket club
(9, 'owner', 'Hockey club'),          -- owned by Hockey club
(10, 'owner', 'Kids Hockey')          -- owned by Hockey club
(11, 'training', 'smart train'),      -- owned by Hockey club
(12, 'training', 'fit train');        -- owned by Hockey club

I'm tried

SELECT name
FROM group
WHERE type = 'training'
GROUP BY ownedByGroup(id)
ORDER BY COUNT(*) DESC
LIMIT 1;

but I keep getting the error:

ERROR: column "name" must appear in the GROUP BY clause or be used in an aggregate function
postgresql sql database
1 Answers

You would group by the ownedByGroup(id) expression and get the max count(*).

After that you would join with the group table, in order to look up the name corresponding to the id as follows

select y.name,y.id 
  from (
    SELECT ownedByGroup(id) as max_training_grp_id
          ,count(*)
      FROM group
     WHERE type = 'training'
    GROUP BY ownedByGroup(id)
    ORDER BY COUNT(*) DESC
    LIMIT 1
       )x
   join group y
     on x.max_training_grp_id=y.id
user_71370900
Related