'SQL Server SubQuery Error - Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I'm trying to select multiple values from 3 subqueries and combine them together. But I get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Here is my code.

SELECT(
SELECT ROUND(
  (SELECT SUM(TotalProcessed) FROM WIP_CONTENT WHERE WipContentClass=1 )
  /(SELECT SUM(TotalProcessed) from WIP_CONTENT WHERE WipContentClass is not null)*100,0) AS [Quality],

(SELECT ROUND(
CAST((SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING') AS float)/
CAST(
((SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS IdleTime FROM RESOURCE_LABOR WHERE  ReasonCode='Idle Time')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS SetupTime FROM RESOURCE_LABOR WHERE  ReasonCode='MACHINE SETUP')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS DownTime FROM RESOURCE_LABOR 
INNER JOIN REASON_CODE RC ON RESOURCE_LABOR.ReasonCode = RC.ReasonCode WHERE  RC.ReasonType='3'))AS FLOAT)*100,0) AS [Availability]),

(SELECT ROUND((
((SELECT SUM(TotalProcessed) AS TotalCount from WIP_CONTENT WHERE WipContentClass is not null)/
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING'))/
15)*100,0) AS [Performance]))

I don't see where I'm selecting multiple columns in my WHERE clause. Appreciate any insight.

sql-serversubquery


Solution 1:[1]

There was an issue in bracketing correctly. Its a complicated combination of subqueries and I didnt realize there was a missing bracket. Here is the code that works

SELECT
(SELECT ROUND(
  (SELECT SUM(TotalProcessed) FROM WIP_CONTENT WHERE WipContentClass=1 )
  /(SELECT SUM(TotalProcessed) from WIP_CONTENT WHERE WipContentClass is not null)*100,0)) AS Quality,

(SELECT ROUND(
CAST((SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING') AS float)/
CAST(
((SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS IdleTime FROM RESOURCE_LABOR WHERE  ReasonCode='Idle Time')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS SetupTime FROM RESOURCE_LABOR WHERE  ReasonCode='MACHINE SETUP')+
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS DownTime FROM RESOURCE_LABOR 
INNER JOIN REASON_CODE RC ON RESOURCE_LABOR.ReasonCode = RC.ReasonCode WHERE  RC.ReasonType='3'))AS FLOAT)*100,0))Availability,

(
SELECT ROUND((
((SELECT SUM(TotalProcessed) AS TotalCount from WIP_CONTENT WHERE WipContentClass is not null)/
(SELECT SUM(DATEDIFF(HOUR, [StartTime], [EndTime])) AS UpTime FROM RESOURCE_LABOR WHERE  ReasonCode='Running' OR ReasonCode='RUNNING'))/
15)*100,0)) AS Performance

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mohi

Related Questions