1

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.

table example

EDITED:

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

forpas
  • 160,666
  • 10
  • 38
  • 76
Friko
  • 44
  • 5
  • you should make this reproducible by providing accurate DDL and test data. Maybe on one of the sql fiddle sites online? I've been enjoying the minimalistic https://dbfiddle.uk/ (no affiliation) – erik258 Oct 02 '22 at 14:02
  • Probably, you want to `COUNT(DISTINCT member.team_id)` – Mihe Oct 02 '22 at 14:15
  • 1
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Georg Richter Oct 02 '22 at 14:18
  • I tried COUNT DISTINCT as suggested but then it only returns 1 in each row. Regarding image, I tried table builder in stack, it looked good in preview but once posted it lost all it's formatting and just printed as text. – Friko Oct 02 '22 at 15:00
  • 1
    Oh, sorry, I didn't take the group by into account. Please provide DDL and data as requested by @erik258. – Mihe Oct 02 '22 at 16:08
  • fiddle data has been added in edited part. hope this helps to troubleshoot as it's clearly replicated error. Thanks a lot. https://www.db-fiddle.com/f/gwWCEa3vaPs1JTgBnwk1wM/0 – Friko Oct 03 '22 at 09:52
  • I think you're trying to accomplish a pivot-query/table, right? Something like described [here](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql). – Sam020 Oct 03 '22 at 10:06
  • What is your expected output for the sample data in the fiddle? – forpas Oct 03 '22 at 10:24
  • Yes technically it would be a pivot table output which I can achieve in Excel, but struggle in SQL. @forpas expected output is exactly as it is now but just with correct total_members, as you see in fiddle now it shows team1 has 34 members and team 2 has 39. but in total there are only 22 members with 11 in each team so it's clearly incorrect and for some reason result is multiplied few times. and in actual database I have tens of thousands of lines so it's hard to check manually. – Friko Oct 03 '22 at 10:31

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76