none
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. RRS feed

  • Question

  • 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.

    Friday, December 27, 2019 7:33 PM

All replies

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

    Friday, December 27, 2019 7:41 PM
    Moderator
  • Hey Jing,

    With your query I am getting everything as 100.

    could you Please check

    Friday, December 27, 2019 8:02 PM
  • You need to provide a sample table and data along with your expected result.

    (in script)

    create table  ... or declare @PhoneTempResults table (....)

    insert into into @PhoneTempResults values(...)

    -- expected result

    Thanks.

    Friday, December 27, 2019 8:10 PM
    Moderator
  • Check this query too:

    Select

       pt.CategoryName,

       pt.AgentDepartmentName,

       pt.TicketsOpened,

       cast(

          case when pt.TicketsOpened = 0 then 0

          else pt.TicketsOpened * 100.0 / (select sum(pt.TicketsOpened) from @PhoneTempResults) end

         as numeric(36,2))

    from @PhoneTempResults pt

    group by pt.CategoryName,pt.AgentDepartmentName,pt.TicketsOpened

    Friday, December 27, 2019 8:25 PM
  • Hey Viorel,

    I am still getting the same error with your query

    Friday, December 27, 2019 8:32 PM
  • Please at least post DDL and some sample data. We now have to guess the keys, the constraints, and all the datatypes. I see you still use the old convert() that we inherited from Sybase; you might want to start using the ANSI/ISO standard cast () instead. It would also help if you knew the ISO 11179 naming conventions so we could get some idea of what's going on. For example, there is no such thing as a category_name in a valid data model. Both those words are what we call attribute properties so we need to have either "<something in particular>_category" or "<something in particular>_name"; I will guess that these are categories of tickets, but I'm guessing because we don't have any specs. I'm also trying to figure out what you're calling "tickets_opened" is actually a count of open tickets or date.

    Here's a guess and what you might be trying to do. Technically, it's a bad idea to mix decimal () and numeric () datatypes. They are different in the ANSI ISO standards.

    SELECT phone_category, agent_department_name, open_ticket_cnt, 
      (CAST(open_ticket_cnt AS DECIMAL(25,5))
         /CASE WHEN SUM(open_ticket_cnt) > 0 
                      THEN SUM(open_ticket_cnt) 
                       ELSE CAST(1.0  AS DECIMAL(25,5)) END) AS open_ticket_ratio
    FROM @Phone_Temp_Results
    GROUP BY phone_category, agent_department_name, open_ticket_cnt; 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 27, 2019 10:15 PM
  • The problem is how your CASE expression is formed - which is causing multiple rows to be returned.

    Try changing it to: SUM(CASE WHEN pt.TicketsOpened > 0 THEN pt.TicketsOpened ELSE 1 END)

    Another option is COALESCE(NULLIF(SUM(pt.TicketsOpened), 0), 1)

    Your final query might look like this:

     Select pt.CategoryName
          , pt.AgentDepartmentName
          , pt.TicketsOpened
          , TotalTicketsOpened = cast(round(coalesce(nullif(sum(pt.TicketsOpened), 0), 1) * 100, 2) As numeric(36,2))
       From @PhoneTempResults   pt
      Group By
            pt.CategoryName
          , pt.AgentDepartmentName
          , pt.TicketsOpened;
    


    Jeff Williams

    Saturday, December 28, 2019 4:22 PM
  • Hi ,

     

    This error message appears when you try to use subquery (correlated or not) that returns more than one value to the calling query. This usually indicates that there are duplicate entries in the column of a table where it's expected to be unique. One of the reasons this may occur, is if the column in the affected table is not constrained to unique identifiers. Try each of the following steps until the issue is resolved.

     

    1.If using a SQL job then launch the SQL Server Profiler to trace the exact location of the issue.

     

    2.Insert a TOP 1 condition on your SELECT statement (ex: SELECT top 1 max(sequence) FROM TableName WHERE sequence_id=MemberShip.id)

     

    3.Check for duplicate numbers in the affected table (ex: SELECT * FROM table where id =(SELECT id FROM table1) --where table1 returns more than 1 value)

     

    4.Use the aggregate function MAX() which will guarantee that exactly only one value is returned and the subquery is syntactically valid. (ex: SELECT o.* FROM Northwind.dbo.Orders o WHERE o.OrderDate = (SELECT MAX(OrderDate) FROM Northwind.dbo.Orders))

     

    5.Use or extend your WHERE clause in the SQL subquery, to filter one or more fields for a unique value.

     

    Please choose the method that suits you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 30, 2019 2:36 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 1, 2020 6:57 AM