Avatar of MeritorWabco
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.
Microsoft SQL ServerMicrosoft SQL Server 2005Databases

Avatar of undefined
Last Comment
MeritorWabco
Avatar of Umar Topia
Umar Topia
Flag of India image

I think you can do 1 thing:

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
Avatar of tim_cs
tim_cs
Flag of United States of America image

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

Open in new window

Avatar of MeritorWabco
MeritorWabco

ASKER

Tim,

There could be 100 different users.  UserA, B, C, D.... etc.
Avatar of Umar Topia
Umar Topia
Flag of India image

MeritorWabco,
In your scenario the solution which I proposed would work properly.
Avatar of MeritorWabco
MeritorWabco

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
Avatar of tim_cs
tim_cs
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Umar Topia
Umar Topia
Flag of India image

Blurred text
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.
Avatar of Umar Topia
Umar Topia
Flag of India image

insert into @mytable
SELECT User2, Count(User2) from yourtable group by User2

insert into @mytable
SELECT User3, Count(User3) from yourtable group by User3

 
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of MeritorWabco
MeritorWabco

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

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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo