CODEWITHSUNDEEP
sql
Maxymus
Maxymus

Reputation: 1480

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.

Upvotes: 0

Views: 524

Answers (5)

vijayakumar
vijayakumar

Reputation: 99

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

Upvotes: 0

Guffa
Guffa

Reputation: 700352

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

Upvotes: 0

Scott Vander Molen
Scott Vander Molen

Reputation: 6439

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

Upvotes: 0

Flipster
Flipster

Reputation: 4401

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.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

you can use

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

But that depends on your intensions...

Upvotes: 2

Related Questions