title: Subquery returned more than 1 value. This is not permitted when the subquery
body: <p>I'm trying to return Windows update compliance data from SCCM using SQL, I've extracted the query from a built in SSRS report. </p>
  
  <p>Instead of running a separate query for each @colname (Group of computers) I'm trying to combine into one query by declaring @colname as a table and inserting multiple values.</p>
  
  <p>The error returned in SQL is:</p>
  
  <blockquote>
    <p>Msg 512, Level 16, State 1, Line 6<br>
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>My code:</p>
  
  <pre><code>DECLARE @title VARCHAR(500);
  DECLARE @colname TABLE (name VARCHAR(100));
  
  SET @title = 'ADR | Workstation Software Updates 2017-12-14 09:01:38';
  
  INSERT INTO @colname   
  VALUES ('All Alpha Workstations'), ('All Beta Workstations'), ('All Delta Workstations');
  
  SELECT DISTINCT
      COUNT(*) [Total Clients], li.title, coll.name,
      SUM(CASE WHEN ucs.status = 3 OR ucs.status = 1 THEN 1 ELSE 0 END) AS 'Installed / Not Applicable',
      SUM(CASE WHEN ucs.status = 2 THEN 1 ELSE 0 END) AS 'Required',
      SUM(CASE WHEN ucs.status = 0 THEN 1 ELSE 0 END) as 'Unknown',
      ROUND((CAST(SUM(CASE WHEN ucs.status = 3 OR ucs.status = 1 THEN 1 ELSE 0 END) AS float) / COUNT(*)) * 100, 2) AS 'Success %',
      ROUND((CAST(COUNT(CASE WHEN ucs.status NOT IN ('3', '1') THEN '*' END) AS FLOAT) / COUNT(*)) * 100, 2) AS 'Not Success%'
  FROM
      v_Update_ComplianceStatusAll UCS
  INNER JOIN
      v_r_system sys ON ucs.resourceid = sys.resourceid
  INNER JOIN 
      v_FullCollectionMembership fcm ON ucs.resourceid = fcm.resourceid
  INNER JOIN
      v_collection coll ON coll.collectionid = fcm.collectionid
  INNER JOIN
      v_AuthListInfo LI ON ucs.ci_id = li.ci_id
  WHERE 
      li.title = @title 
      AND coll.name = (SELECT name FROM @colname) --and ucs.status=2
  GROUP BY
      li.title, coll.name
  ORDER BY
      1
  </code></pre>
  
  <p>Any help appreciated.
  Thanks</p>