Click here to Skip to main content
15,906,467 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Username       WorkArea      Scope      Qulaity%
aa,bb,cc        xyz           zyx        100
aa,bb,dd        xyz           zyx        99

Now i want the sum of the qulaity% based on the username.
For Example
Username      total
 aa            199
 bb            199
 dd            99
 cc            100


I want to sum the quality of each user but they are delimited by commas. As aa is in both the rows its Quality sum should be 199. As cc is in the single row his sum should 100, etc.
Posted
Updated 11-May-15 20:12pm
v3
Comments
Maciej Los 12-May-15 1:55am    
Based on what codition? Did you forget about 'cc'?
King Fisher 12-May-15 1:59am    
what is the logic? it not clear to understand the logic, update your question pls.

Check this:
SQL
DECLARE @tmp TABLE (Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)

INSERT INTO @tmp (Username, WorkArea, Scope, Quality)
VALUES('aa,bb,cc,ee,ff,gg,hh,ii', 'xyz', 'zyx', '100'),
('aa,bb,dd,ii,kk', 'xyz', 'zyx', '99'),
('cc,gg,ee,hh,kk', 'xyz', 'zyx', '77')

;WITH UserTotals AS
(
        -- initial values
        --multiple users
	SELECT LEFT(Username, CHARINDEX(',', Username)-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Username)>0
      UNION ALL
        --single user only
	SELECT Username AS SingleUser, Quality, NULL AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Username)=0
        -- here starts recursive part
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM UserTotals
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS SingleUser, Quality, NULL AS Remainder
	FROM UserTotals
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT SingleUser, SUM(Quality) AS Total
FROM UserTotals
GROUP BY SingleUser


Result:
aa	199
bb	199
cc	177
dd	99
ee	177
ff	100
gg	177
hh	177
ii	199
kk	176


Note: above example uses CTE[^] (recursive query) to split Username into single user name. For further information, please see: Using Common Table Expressions[^]

[EDIT]
CTE query has been updated according to OP comments.
 
Share this answer
 
v5
Comments
Sergey Alexandrovich Kryukov 12-May-15 2:11am    
...but, speaking of understanding by the inquirer: this is nice, you can use it to explain things; a 5.
—SA
Maciej Los 12-May-15 2:13am    
Thank you, Sergey.
King Fisher 12-May-15 2:17am    
nice maciej master :) ma 5.
Maciej Los 12-May-15 3:08am    
Thank you, King ;)
Maciej Los 12-May-15 3:09am    
It's global solution! Have you tried?
Don't use different data items in one column, comma-separated or separated in any other way. This a total abuse of relational model and programming in general.

All attributes in relational database should be atomic. In other words, you should have separate table of elements as aa, bb or cc. Some other table could be called "group", and another one could be called "groupMembership"; it could associate elements with groups. And then, in the table you show, include foreign key referencing a group, instead of "UserName". This way, all queries will be done in SQL, without parsing your lists. This schema could vary, depending on your goals, constraints, etc. I hope you got the idea.

—SA
 
Share this answer
 
Comments
Maciej Los 12-May-15 2:08am    
Holy true! +5!
Sergey Alexandrovich Kryukov 12-May-15 2:10am    
Thank you, Maciej. Not sure the inquirer will understand this. Let's see...
—SA
King Fisher 12-May-15 2:20am    
Definitely, the OP will understand sir. its more clear 5+
Sergey Alexandrovich Kryukov 12-May-15 2:26am    
Thank you.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900