Subquery returned more than 1 value how to handle it

My SQL Query is

  select ID, ModuleID,
         (SELECT ((CAmount * CPercentage)/100)  FROM Table1 ) as 开发者_开发知识库Percentage 
    from Table1 
order by ModuleID ;

It works perfectly if there is only one value but if there is more than one value i get an error. how to handle it, i want all the amount in the table to be converted to its corresponding percentage please help.

you can use

SELECT TOP 1 ((CAmount * CPercentage)/100)  FROM Table1 

But that depends on your intensions...

Or...

  select ID, ModuleID,
         (SELECT ((SUM(CAmount) * SUM(CPercentage))/100)  FROM Table1 ) as Percentage 
    from Table1 
order by ModuleID ;

That will successfully handle multiple values.

It strikes me as slightly strange that your subquery is using the same table as the main query. It looks like you are trying to get the percentage for each module, so getting the percentage for the whole table might be the wrong result.

Unless I'm misunderstanding your intention, I would expect this to be the correct query:

SELECT ID, ModuleID, SUM((CAmount * CPercentage)/100) AS Percentage 
FROM Table1 
ORDER BY ModuleID

If you want to calculate the value for each record, you don't need a subquery at all:

select ID, ModuleID, (CAmount * CPercentage) / 100 as Percentage 
from Table1
order by ModuleID

You can use This split with ","

(SELECT distinct DocumentList=stuff((SELECT distinct (', '+CCD2.document_filename + ' ' ) from callcenter_documents CCD2
WHERE document_answerid =AnswerID for XML path('')),1,2,'') from callcenter_documents CCD1
WHERE document_answerid =AnswerID) AS document_filename