locked
Group By giving me fits RRS feed

  • Question

  • User-718146471 posted

    Hey folks, I'm trying to figure something out and it is making me nuts. I'm trying to give the total sum of items by team. That works ok but what happens is I have a weird result. A couple teams give more than one result. This is kind of what I get:

    Team Name		Projects
    Team1			3606
    Team2			2632
    Team3			2599
    Team4			456
    Team5			88
    Team5			62
    Team5			14
    Team5			0
    Team5			0
    Team6			0
    Team7			0

    What I want is for all that are team5 to be one total instead of 5 individual totals. Any ideas? Here's my query.

    SELECT CASE WHEN Team IN ('Team5-a', 'Team5-b', 'Team5-c', 'Team5-d', 'Team5-e', 
         'Team5-f') THEN 'Team5' WHEN Team 
         IN ('Team1-a') THEN 'Team1' 
         WHEN Team IN ('Team2-a') 
         THEN 'Team2' WHEN Team IN ('Team3-a') 
         THEN 'Team3' WHEN Team IN ('Team4-a') 
         THEN 'Team4' WHEN Team IN ('Team6-a') 
         THEN 'Team6' WHEN Team IN ('Team7-a') 
         THEN 'Team7' 
         ELSE Team END AS 'Team Name', SUM(CAST(ItemCount as int)) Items
    FROM RawData
    GROUP BY Team
    ORDER BY Items DESC, Team

    Monday, February 3, 2020 4:29 PM

All replies

  • User753101303 posted

    Hi,

    Likely because you group on Team (ie the full name) rather than on [Team name]. You can't use the alias, you would have to copy/paste the expression in the GROUP BY clause or use a SELECT * FROM (SELECT * FROM etc..) AS src GROUP BY ... query...

    Another problem is storing two pieces of data into the same column and so you have to hardcode the whole correspondance table into your query  (you are sure "Team4" should be shown for "Team6-a" ???)

    I would prefer something such as:

    CREATE TABLE RawData(Team VARCHAR(10),Instance CHAR(1),ItemCount INT)
    INSERT INTO RawData
    VALUES ('Team1','',3606),
    ('Team2','',2632),
    ('Team3','',2599),
    ('Team4','',456),
    ('Team5','a',88),
    ('Team5','b',62),
    ('Team5','c',14),
    ('Team5','d',0),
    ('Team5','e',0),
    ('Team6','',0),
    ('Team7','',0)
    
    SELECT [Team],SUM(ItemCount) FROM RawData GROUP BY Team
    ORDER BY SUM(ItemCount) DESC 

    Monday, February 3, 2020 4:51 PM
  • User77042963 posted

    I suggest you to post your table DDL and sample data inserts for TSQL questions in the future.

    Thanks.

    Monday, February 3, 2020 5:02 PM
  • User-718146471 posted

    Ok, will do. Thank you.

    Monday, February 3, 2020 5:50 PM
  • User-719153870 posted

    Hi bbcompent1,

    Is is possible that you can provide the table's structure and some sample data so that we can reproduce and solve the problem by delivering the correct query?

    Or if the problem has been solved, you can mark the post which helps to solve the problem as the answer.

    Best Regard,

    Yang Shen

    Tuesday, February 4, 2020 2:19 AM