How to Count Rows based on column value after it's grouped by id-CodePudding

How to return total number of rows based on column value which is grouped by, below code works and prints distinct teams with their items ownership but total_member count is incorrect and around 10x higher than it actually is, looks like each CASE WHEN multiplies row count.

Here is my code:

SELECT
    team.team_name,
    team_rank.points,
    SUM(CASE WHEN items.item_id = 1 THEN items.count END) AS item1,
    SUM(CASE WHEN items.item_id = 2 THEN items.count END) AS item2,
    SUM(CASE WHEN items.item_id = 3 THEN items.count END) AS item3,
    SUM(CASE WHEN items.item_id = 4 THEN items.count END) AS item4,
    COUNT(member.team_id) AS total_members
FROM
    member
INNER JOIN 
    items ON member.obj_Id = items.owner_id
INNER JOIN 
    team ON team.team_id = member.team_id
JOIN 
    team_rank ON team.team_id = team_rank.team_id 
GROUP BY
    member.team_id

below is current result, item calculations are correct, but actually team 1 has only 19 members and team 2 has 5, so it's multiplied many times.

EDITED:

please see example database data uploaded in fiddle as requested. https://www.db-fiddle.com/f/gwWCEa3vaPs1JTgBnwk1wM/0

CodePudding user response：

Aggregate inside member to get total_members and join the results to the query:

SELECT
    team.team_name,
    team_rank.points,
    SUM(CASE WHEN items.item_id = 1 THEN items.count END) AS item1,
    SUM(CASE WHEN items.item_id = 2 THEN items.count END) AS item2,
    SUM(CASE WHEN items.item_id = 3 THEN items.count END) AS item3,
    SUM(CASE WHEN items.item_id = 4 THEN items.count END) AS item4,
    c.total_members
FROM member
INNER JOIN (SELECT team_id, COUNT(team_id) total_members FROM member GROUP BY team_id) c
ON c.team_id = member.team_id
INNER JOIN items ON member.obj_Id = items.owner_id
INNER JOIN team ON team.team_id = member.team_id
INNER JOIN team_rank ON team.team_id = team_rank.team_id 
GROUP BY member.team_id;

See the demo.