CODEWITHSUNDEEP
sqlstored-proceduresjoinsql-server-2008-r2
Novice
Novice

Reputation: 558

Stored procedure for getting sum of entries in table for each ID

I am writing a stored procedure, where I have a column called scale which stores the result from the radio button selected as 1/ 2/ 3/ 4 for each type of skill name.

Now, I want to see total number of people under each scale- 1 and 2 and 3 and 4 for a particular skillname1, skillname 2,..., skillname20.

Here is my table:

tblSkill: 

ID | SkillName

and another table as:

tblskillMetrics: 

ID | SkillID | EmployeeID | Scale

And here is the query am trying to write:

Create Procedure spGetSkillMetricsCount
As
Begin
   SELECT 
      tblSkill.Name as skillname,
      (select COUNT(EmployeeID) from tblSkillMetrics where tblSkillMetrics.Scale=1) AS NotAplicable,
      (select COUNT(EmployeeID) from tblSkillMetrics where tblSkillMetrics.Scale=2 ) AS Beginner,
      (select COUNT(EmployeeID) from tblSkillMetrics where tblSkillMetrics.Scale=3 ) AS Proficient,
      (select COUNT(EmployeeID) from tblSkillMetrics where tblSkillMetrics.Scale=4 ) AS Expert
   FROM
      tblSkill
   INNER JOIN 
      tblSkillMetrics ON tblSkillMetrics.SkillID = tblSkill.ID
   GROUP BY 
      tblSkillMetrics.Scale, tblSkill.Name 
   ORDER BY 
      skillname DESC
END

By using this stored procedure, I am able to get the desired format in which I want the result but in the output for each : Not Applicable, Beginner, Proficient or Expert is same and it is sum of all the entries made in the table.

Please can someone suggest where am I going wrong.

Upvotes: 2

Views: 4084

Answers (3)

Marsh
Marsh

Reputation: 173

You can use something like this: 

SELECT sum(case when tblskillmetrics.scale = 2 then 1 else 0 end) Beginner,  
       sum(case when tblskillmetrics.Scale=3 then 1 else 0 end)Proficient, 
       SUM(case when tblSkillMetrics.Scale=4 then 1 else 0 end)Expert,
       tblSkillGroup.ID AS GroupID,tblSkillGroup.Name AS GroupName,
       tblSkill.ID AS SkillID 
       , tblSkill.Name AS SkillName

FROM  tblSkill INNER JOIN tblSkillMetrics 
         ON tblSkillMetrics.SkillID=tblSkill.ID 
ORDER BY GroupName DESC

Upvotes: 0

Andriy M
Andriy M

Reputation: 77657

Logically, you are grouping by two criteria, scale and skill name. However, if I understand it correctly, every row is supposed to represent a single skill name. Therefore, you should group by tblSkill.Name only. To get different counts for different scales in separate columns, you can use conditional aggregation, i.e. aggregation on an expression that (usually) involves a CASE construct. Here's how you could go about it:

SELECT 
   tblSkill.Name AS skillname,
   COUNT(CASE tblSkillMetrics.Scale WHEN 1 THEN EmployeeID END) AS NotAplicable,
   COUNT(CASE tblSkillMetrics.Scale WHEN 2 THEN EmployeeID END) AS Beginner,
   COUNT(CASE tblSkillMetrics.Scale WHEN 3 THEN EmployeeID END) AS Proficient,
   COUNT(CASE tblSkillMetrics.Scale WHEN 4 THEN EmployeeID END) AS Expert
FROM
   tblSkill
INNER JOIN 
   tblSkillMetrics ON tblSkillMetrics.SkillID = tblSkill.ID
GROUP BY 
   tblSkill.Name 
ORDER BY 
   skillname DESC
;

Note that there's a special syntax for this kind of queries. It employs the PIVOT keyword, as what you get is essentially a grouped result set pivoted on one of the grouping criteria, scale in this case. This is how the same could be achieved with PIVOT:

SELECT
   skillname,
   [1] AS NotAplicable,
   [2] AS Beginner,
   [3] AS Proficient,
   [4] AS Expert
FROM (
   SELECT 
      tblSkill.Name AS skillname,
      tblSkillMetrics.Scale,
      EmployeeID
   FROM
      tblSkill
   INNER JOIN 
      tblSkillMetrics ON tblSkillMetrics.SkillID = tblSkill.ID
) s
PIVOT (
   COUNT(EmployeeID) FOR Scale IN ([1], [2], [3], [4])
) p
;

Basically, PIVOT implies grouping. All columns but one in the source dataset are grouping criteria, namely every one of them that is not used as an argument of an aggregate function in the PIVOT clause is a grouping criterion. One of them is also assigned to be the one the results are pivoted on. (Again, in this case it is scale.)

Because grouping is implicit, a derived table is used to avoid grouping by more criteria than necessary. Values of Scale become names of new columns that the PIVOT clause produces. (That is why they are delimited with square brackets when listed in PIVOT: they are not IDs in that context but identifiers delimited as required by Transact-SQL syntax.)

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20794

A case construct instead of all those subqueries might work.

select tblSkill.name skillname
, case when tblSkillMetrics = 1 then 'Not Applicable'
etc
else 'Expert' end level
, count(employeeid) records
from tblSkill join tblSkillMetrics 
on tblSkillMetrics.SkillID = tblSkill.ID
group by tblSkill.name
, case when tblSkillMetrics = 1 then 'Not Applicable'
etc
else 'Expert' end level
order by skillname desc

Upvotes: 1

Related Questions