Last post Feb 04, 2020 02:19 AM by Yang Shen
Feb 03, 2020 04:29 PM|bbcompent1|LINK
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
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')
ELSE Team END AS 'Team Name', SUM(CAST(ItemCount as int)) Items
GROUP BY Team
ORDER BY Items DESC, Team
Feb 03, 2020 04:51 PM|PatriceSc|LINK
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
SELECT [Team],SUM(ItemCount) FROM RawData GROUP BY Team
ORDER BY SUM(ItemCount) DESC
Feb 03, 2020 05:02 PM|limno|LINK
I suggest you to post your table DDL and sample data inserts for TSQL questions in the future.
Feb 03, 2020 05:50 PM|bbcompent1|LINK
Ok, will do. Thank you.
Feb 04, 2020 02:19 AM|Yang Shen|LINK
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.