Skip navigation
Contact Us
1118 Views 7 Replies Latest reply: Feb 14, 2013 10:30 PM by Manojkumar Venkatachalam RSS
patil kumar Newbie 8 posts since
Dec 11, 2012
Currently Being Moderated

Feb 13, 2013 12:07 AM

sql is not working for group by clause

Hi folks,

 

Below sql query is not working due to conflit in group by classs

it throughing an error ORA-00937..could some pls help on this...

 

SELECT

MAX(SUM (NVL(WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY,0))) as RELEASED_QTY,SOURCE_HEADER_ID as SOURCE_HEADER_ID,SOURCE_LINE_ID as SOURCE_LINE_ID

from WSH_DELIVERY_DETAILS

WHERE WSH_DELIVERY_DETAILS.RELEASED_STATUS IN ('C', 'Y', 'D') OR WSH_DELIVERY_DETAILS.ATTRIBUTE7 = 'B'

group by SOURCE_HEADER_ID,SOURCE_LINE_ID SELECT
MAX(SUM (NVL(WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY,0))) as RELEASED_QTY,SOURCE_HEADER_ID as SOURCE_HEADER_ID,SOURCE_LINE_ID as SOURCE_LINE_ID
from WSH_DELIVERY_DETAILS
WHERE WSH_DELIVERY_DETAILS.RELEASED_STATUS IN ('C', 'Y', 'D') OR WSH_DELIVERY_DETAILS.ATTRIBUTE7 = 'B'
group by SOURCE_HEADER_ID,SOURCE_LINE_ID

 

 

Error - Not a single group by function

 

 

Thanks,

Anand kumar

  • Dineshkumar Nakka Novice 77 posts since
    May 31, 2011
    Currently Being Moderated
    Feb 13, 2013 12:22 AM (in response to patil kumar)
    sql is not working for group by clause

    Try the query by keeping the aggregate function as last column and execute.

     

    SELECT

    SOURCE_HEADER_ID as SOURCE_HEADER_ID,SOURCE_LINE_ID as  SOURCE_LINE_ID,

    MAX(SUM  (NVL(WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY,0))) as  RELEASED_QTY

    from WSH_DELIVERY_DETAILS

    WHERE WSH_DELIVERY_DETAILS.RELEASED_STATUS IN ('C', 'Y', 'D') OR WSH_DELIVERY_DETAILS.ATTRIBUTE7 = 'B'

    group by SOURCE_HEADER_ID,SOURCE_LINE_ID

     

    Thanks

    Dinesh

    • vasu bhattiprolu Apprentice 83 posts since
      Apr 22, 2012
      Currently Being Moderated
      Feb 13, 2013 2:31 AM (in response to Dineshkumar Nakka)
      Re: sql is not working for group by clause

      Hi  Patil,

       

      Try this

       

      SELECT

      SOURCE_LINE_ID,SOURCE_HEADER_ID,REQUESTED_QUANTITY

      FROM

      WSH_DELIVERY_DETAILS

      WHERE REQUESTED_QUANTITY =(SELECT MAX(RELEASED_QTY) AS REQUESTED_QUANTITY FROM (

      SELECT SOURCE_LINE_ID,SOURCE_HEADER_ID,SUM(NVL(REQUESTED_QUANTITY,0)) AS RELEASED_QTY FROM

      WSH_DELIVERY_DETAILS WHERE << PLACE UR CONDITIONS>>

      GROUP BY SOURCE_LINE_ID,SOURCE_HEADER_ID))

       

      The above qry serves the purpose when u r trying to pull the max qty  combination out of all summed up combinations.

       

      Could you please come up with requirement 

       

      Regards,

       

      Srinivas

      

  • Jaspreet Banga Expert 526 posts since
    Jun 14, 2011
    Currently Being Moderated
    Feb 13, 2013 1:28 PM (in response to patil kumar)
    sql is not working for group by clause

    SELECT SOURCE_HEADER_ID,SOURCE_LINE_ID, max (RELEASED_QTY)

    from

    (SELECT

    SUM(NVL(WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY,0)) as RELEASED_QTY,

    SOURCE_HEADER_ID as SOURCE_HEADER_ID,

    SOURCE_LINE_ID as SOURCE_LINE_ID

    from WSH_DELIVERY_DETAILS

    WHERE WSH_DELIVERY_DETAILS.RELEASED_STATUS IN ('C', 'Y', 'D') OR WSH_DELIVERY_DETAILS.ATTRIBUTE7 = 'B'

    group by SOURCE_HEADER_ID,SOURCE_LINE_ID)

    group by SOURCE_HEADER_ID,SOURCE_LINE_ID

     

    Thnx,

    Jas

    • Manojkumar Venkatachalam Novice 82 posts since
      Dec 18, 2012
      Currently Being Moderated
      Feb 13, 2013 11:05 PM (in response to Jaspreet Banga)
      Re: sql is not working for group by clause

      Hi Jaspreet ,

       

      • The above Query will give only the SUM( REQUESTED_QUANTITY ) for each Group available in ( SOURCE_HEADER_ID , SOURCE_LINE_ID )
      • There is no use of Outer Query since both Sub-Query and Main Query will give same result.
      • So we can find only MAX ( REQUESTED_QUANTITY ) in Main query as follows :

       

      SELECT MAX(RELEASED_QTY) RELEASED_QTY FROM

      (SELECT (SUM(NVL(WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY,0))) as RELEASED_QTY,

      SOURCE_HEADER_ID, SOURCE_LINE_ID

      from WSH_DELIVERY_DETAILS

      WHERE WSH_DELIVERY_DETAILS.RELEASED_STATUS IN ('C', 'Y', 'D')

      OR WSH_DELIVERY_DETAILS.ATTRIBUTE7 = 'B'

      group by SOURCE_HEADER_ID,SOURCE_LINE_ID )

  • Manojkumar Venkatachalam Novice 82 posts since
    Dec 18, 2012
    Currently Being Moderated
    Feb 13, 2013 10:35 PM (in response to patil kumar)
    Re: sql is not working for group by clause

    Hi Patil ,

     

    • You want to find the Maximum of REQUESTED_QUANTITY among all the available

           Groups of ( SOURCE_HEADER_ID,SOURCE_LINE_ID ) ?

More Like This

  • Retrieving data ...

Bookmarked By (0)