Subquery returned more than 1 value.

Question

Subquery returned more than 1 value.

Krish319

Old Hand

Points: 352
More actions
December 27, 2019 at 8:38 pm

#3710863

I am running below query and getting this error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Query :
select
pt.CategoryName ,pt.AgentDepartmentName ,pt.TicketsOpened ,cast(round((pt.TicketsOpened/convert(decimal(16,2),(select case when sum(pt.TicketsOpened)> 0 then sum(pt.TicketsOpened) else 1 end from @PhoneTempResults)))*100,2) as numeric(36,2)) from @PhoneTempResults
pt group by pt.CategoryName,pt.AgentDepartmentName,pt.TicketsOpened
''' Please help me to fix this.
December 27, 2019 at 9:29 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you have two FROM statements; it treats the inner FROM as an accidental cross join; i think if you simply remove it you get the results you would expect.

this is untested without sample data:

select
pt.CategoryName ,
pt.AgentDepartmentName ,
pt.TicketsOpened ,cast(round((pt.TicketsOpened/convert(decimal(16,2),(select case when sum(pt.TicketsOpened)> 0 then sum(pt.TicketsOpened) else 1 end )))*100,2) as numeric(36,2)) 
from @PhoneTempResults pt 
group by 
pt.CategoryName,
pt.AgentDepartmentName,
pt.TicketsOpened

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

You should use a windowed SUM:

select pt.CategoryName,
       pt.AgentDepartmentName,
       pt.TicketsOpened,
       CONVERT(numeric(18,2),pt.TicketsOpened/(SUM(pt.TicketsOpened) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)+0.0)*100) PercentageoOfTotalTicketsOpened
  from @PhoneTempResults pt 
 group by pt.CategoryName, pt.AgentDepartmentName, pt.TicketsOpened

Then there is no need for any sort of subquery.