Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Question Unanswered: "Subquery returned more than one result" when it's desired

    Hi,

    I am have the following query:

    Code:
    SELECT COUNT(ws2.WorkerID) AS workercount, s1.SkillName, 
    	(select COUNT(s2.SkillID) as workercount--,s2.SkillName
         from WorkerSkills as ws1 
    	   inner join Workers on ws1.WorkerID = Workers.WorkerID 
    	   INNER Join Skills as s2 on ws1.SkillID=s2.SkillID
    	  where TeamID=11
    	  group by s2.SkillID ) as teamcount
    FROM  Skills as s1
    INNER JOIN WorkerSkills as ws2 ON s1.SkillID = ws2.SkillID 
    INNER JOIN Workers ON ws2.WorkerID = Workers.WorkerID
    WHERE Workers.TeamID=11
    group by s1.SkillName
    And I am getting the following error:
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    which I understand why.
    The problem is that, in my case, I want the teamcount column to have a specific value for each row. And since I am grouping both queries by skillName or skillID, that should work.

    How do I write this query so that I won't get this error but get the results that I want ?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    It's not clear to me what you really want. But I do know that the "group by s2.SkillID ) as teamcount" is causing the error message.

    If you want to have an overview of the number of workers in teamID 11 who have a particular skill, this should do the trick:
    Code:
    SELECT COUNT(*) AS workercount, 
        s.SkillName 
    FROM  Skills as s
        INNER JOIN WorkerSkills as ws ON s.SkillID = ws.SkillID 
        INNER JOIN Workers as W ON ws.WorkerID = W.WorkerID
    WHERE W.TeamID = 11
    group by s.SkillName
    WorkerSkills has all the information about workers per skill. The extra INNER JOINs are only there to get the SkillName and the TeamID (as you already showed in your query).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Thanks for your reply Wim.

    The output I need is as follows

    SkillName NumberOfSkilledWorkers NumberofSkilledWorkersInATeam


    and I only want to display the skills that are related to a specific team. There are n skills on the skills table but team x only have workers with n-5 skills, for instance. So, all I want to display is n -5 skills, how many workers who possess this skills there are in the entire project and how many workers with this skills there are in team x.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    First retain only those skills that are present in 1 team, then JOIN those with all workers.
    Code:
    SELECT    s.SkillName, 
        COUNT(*) AS NumberOfSkilledWorkers, 
        SUM(CASE WHEN W.TeamID = 11 THEN 1 ELSE 0 END) as NumberofSkilledWorkersInATeam
    FROM (SELECT distinct Skills.SkillID, Skills.SkillName 
            FROM Skills
                INNER JOIN WorkerSkills ON Skills.SkillID = WorkerSkills.SkillID 
                INNER JOIN Workers ON WorkerSkills.WorkerID = Workers.WorkerID
            WHERE Workers.TeamID = 11
            ) AS s -- skills in one special team
        INNER JOIN WorkerSkills as ws ON s.SkillID = ws.SkillID 
        INNER JOIN Workers as W ON ws.WorkerID = W.WorkerID
    group by s.SkillName
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Sep 2010
    Posts
    3

    Smile

    Perfect!!! You're the man!

    Thank you so much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •