I have a requirement to count specific/Similar values which has occured in one or more columns. Here's what I mean.
I have a table tbl_Audit_Sheet. I have columns Empid, Emp_Name, Score, Attrib1, Attrib2, Attrib3, Attrib4, etc etc
I have 3 rows specially for attributes for any employee. So In these 3 attributes attrib1, attrib2, attrib3 the possible values comes from 40 options. The end user selects which ever attributes he want from dropdown. He is allowed to put only 3 attributes
per employee and can hence choose any three options from avaliable 40 options.
Now After user inputs any 3 attributes, it store in my table as attrib1, attrib2, attrib3. Now I need to show the occurance of those attributes per employee.
Eg: Taking this
insert into #temp select 768984, 'DeadAir', 'DeadAir', 'DeadAir', 'DeadAir'
insert into #temp select 658479, 'VerbalNods', 'NoHold', 'NoHold', 'DeadAir'
Now I dont want to pivot it because I have other rows like emp_name, manager, audit date etc etc.
1. I need to create a query to find how many times does empid = 768984 has got "DeadAir" as attribute and example again how many times does 658479 has verbal nods, no hold, dead air etc in those 40 options
2. Overall How many people have used specific "Attrib"
Create a derived table (or CTE) that uses UNION ALL to show the relationship of empid to one attribute instance at a time. Use GROUP BY on that derived table to count the occurrences of each combo, and JOIN to it in an outer table to get final results.
In psuedo-code (since I don't know your column names) it will look something like this:
SELECT empid, attribute, AttCount, othercolumns
FROM ( SELECT empid, attribute, COUNT(*) AS AttCount
FROM (
SELECT empid, attrib1 AS attribute FROM MyTable
UNION ALL
SELECT empid, attrib2 AS attribute FROM MyTable
UNION ALL
SELECT empid, attrib3 AS attribute FROM MyTable
) t1
GROUP BY empid, attribute
)t3
INNER JOIN TableContainingOtherColumns t2
ON t1.empid=t2.empid
shreenidhi
Member
122 Points
76 Posts
Count Occurrences of specific value(s) in Row/Column per record!
Jan 29, 2013 07:09 PM|LINK
I have a requirement to count specific/Similar values which has occured in one or more columns. Here's what I mean.
I have a table tbl_Audit_Sheet. I have columns Empid, Emp_Name, Score, Attrib1, Attrib2, Attrib3, Attrib4, etc etc
I have 3 rows specially for attributes for any employee. So In these 3 attributes attrib1, attrib2, attrib3 the possible values comes from 40 options. The end user selects which ever attributes he want from dropdown. He is allowed to put only 3 attributes per employee and can hence choose any three options from avaliable 40 options.
Now After user inputs any 3 attributes, it store in my table as attrib1, attrib2, attrib3. Now I need to show the occurance of those attributes per employee.
Eg: Taking this
1. I need to create a query to find how many times does empid = 768984 has got "DeadAir" as attribute and example again how many times does 658479 has verbal nods, no hold, dead air etc in those 40 options
2. Overall How many people have used specific "Attrib"
Im Bit confused!
Any Help is greatly appreciated
Thanks!
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Count Occurrences of specific value(s) in Row/Column per record!
Jan 29, 2013 07:40 PM|LINK
Create a derived table (or CTE) that uses UNION ALL to show the relationship of empid to one attribute instance at a time. Use GROUP BY on that derived table to count the occurrences of each combo, and JOIN to it in an outer table to get final results.
In psuedo-code (since I don't know your column names) it will look something like this: