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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 27, 2019 at 10:07 pm

  #3710876

  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

  December 28, 2019 at 12:04 am

  #3710886

  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.