Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Jan 29, 2013 07:40 PM by TabAlleman
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
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"
Im Bit confused!
Any Help is greatly appreciated
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:
SELECT empid, attribute, AttCount, othercolumns
FROM ( SELECT empid, attribute, COUNT(*) AS AttCount
SELECT empid, attrib1 AS attribute FROM MyTable
SELECT empid, attrib2 AS attribute FROM MyTable
SELECT empid, attrib3 AS attribute FROM MyTable
GROUP BY empid, attribute
INNER JOIN TableContainingOtherColumns t2