MeritorWabco
asked on
Counting the occurences an item shows up in multiple fields
Hi. I have a table that is structured as the following:
TestRecord
User1
User2
User 3
so the data looks like this
Pass UserA UserB UserC
Pass UserB UserC Null
Fail UserA UserB Null
Pass UserA UserB Null
Pass UserC UserA Null
Pass UserB UserA UserC
Any ideas on how to write a sql statement that will count the number of occurences a specific user shows up in a record.
So....
UserA 5
User B 5
USer C 4
I am not even sure where to start or of this is possible.
TestRecord
User1
User2
User 3
so the data looks like this
Pass UserA UserB UserC
Pass UserB UserC Null
Fail UserA UserB Null
Pass UserA UserB Null
Pass UserC UserA Null
Pass UserB UserA UserC
Any ideas on how to write a sql statement that will count the number of occurences a specific user shows up in a record.
So....
UserA 5
User B 5
USer C 4
I am not even sure where to start or of this is possible.
Last Comment
Try this out.
SELECT 'UserA', SUM(CASE WHEN CHARINDEX('UserA',ISNULL(User1,'') + ISNULL(User2,'') + ISNULL(User3,''),0) <> 0 THEN 1 ELSE 0 END) UserCount
FROM Table1
UNION ALL
SELECT 'UserB', SUM(CASE WHEN CHARINDEX('UserB',ISNULL(User1,'') + ISNULL(User2,'') + ISNULL(User3,''),0) <> 0 THEN 1 ELSE 0 END) UserCount
FROM Table1
UNION ALL
SELECT 'UserC', SUM(CASE WHEN CHARINDEX('UserC',ISNULL(User1,'') + ISNULL(User2,'') + ISNULL(User3,''),0) <> 0 THEN 1 ELSE 0 END) UserCount
FROM Table1
ASKER
Tim,
There could be 100 different users. UserA, B, C, D.... etc.
There could be 100 different users. UserA, B, C, D.... etc.
MeritorWabco,
In your scenario the solution which I proposed would work properly.
In your scenario the solution which I proposed would work properly.
ASKER
Yeah I am working on it right now. Sadly I have never had to create a temporary table before so I am working out the syntax right now.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
insert into @mytable
SELECT User2, Count(User2) from yourtable group by User2
insert into @mytable
SELECT User3, Count(User3) from yourtable group by User3
SELECT User2, Count(User2) from yourtable group by User2
insert into @mytable
SELECT User3, Count(User3) from yourtable group by User3
Use a UNION ALL on each coulmn, then query as if one table, assuming your table is called 'usertable '
SELECT UserX, count(UserX) AS UserCount FROM(select user1 UserX from usertable union all select user2 UserX from usertable union all select user3 UserX from usertable) zz
WHERE UserX IS NOT NULL
GROUP BY UserX
SELECT UserX, count(UserX) AS UserCount FROM(select user1 UserX from usertable union all select user2 UserX from usertable union all select user3 UserX from usertable) zz
WHERE UserX IS NOT NULL
GROUP BY UserX
ASKER
Thanks for your help I was able to get both solutions to work. I was able to get Tim's to work faster but that is just because of my skill set.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
1) Create one Temporary Table with 3 Columns
ID
UserName
Count
2) Fire a GroupBy Query on User1 Column with selection Count (in your main table)
and then add this data to you temprary table
3) and 4) do the same for Column2 and Column3
Then refer your temprary table for final summary