1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Getting Not a single group function when using SUM(CASE WHEN ( ...))

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Feb 9, 2010.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Good Afternoon To All,

    I using SUM(CASE WHEN (...)) similar to the following query, but I get ORA-00937: Not a single group function error. If I remove the first line, then it works correctly, but I also need to use the first line too. Can some one please give me an example how to fix this?


    Thanks With Respect,
    Farshad


    SELECT
    vw_cc_vehicle_disloc.ZONE AS Zone,
    ccx_ersdispatch.ext_dispatchOriginalETA ,
    SUM(CASE WHEN (ccx_ersdispatch.ext_dispatchOriginalETA >= 0 AND ccx_ersdispatch.ext_dispatchOriginalETA <= 15)
    THEN COUNT(ccx_ersdispatchOffer.ext_OfferAccepted)
    END) AS LM15
    FROM
    vw_cc_provider_businfo
    WHERE
    AND vw_cc_provider_businfo.LOB = :LOB
    AND vw_cc_provider_businfo.network = :Network
    AND vw_cc_provider_businfo.ZONE = :Zone
    AND vw_cc_provider_businfo BETWEEN
    (Last_Day(ADD_MONTHS(SYSDATE,-2))+1) AND ( Last_Day(ADD_MONTHS(SYSDATE,-1)))
    GROUP BY
    vw_cc_vehicle_disloc.ZONE,
    ccx_ersdispatch.ext_dispatchOriginalETA
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Possibly I'm missing something but I cannot understand how this query runs, much less how it returns any useful data. None of the tables/views in your select list are found in the FROM clause of that query.

    While you're explaining this query also post the Oracle version you're using.
     
    Farshad Javadi likes this.
  3. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Dear Zargon,

    I am really sorry for the first incorrect-query posting.

    Here is the correct query:

    SELECT
    vw_cc_provider_businfo.ZONE AS Zone,
    ccx_ersdispatchOffer.ext_dispatchOriginalETA ,
    SUM(CASE WHEN (ccx_ersdispatchOffer.ext_dispatchOriginalETA >= 0 AND ccx_ersdispatchOffer.ext_dispatchOriginalETA <= 15)
    THEN COUNT(ccx_ersdispatchOffer.ext_OfferAccepted)
    END) AS LM15
    FROM
    vw_cc_provider_businfo,
    ccx_ersdispatchOffer
    WHERE
    vw_cc_provider_businfo = ccx_ersdispatch.ext_Providerid
    AND vw_cc_provider_businfo.LOB = :LOB
    AND vw_cc_provider_businfo.network = :Network
    AND vw_cc_provider_businfo.ZONE = :Zone
    AND vw_cc_provider_businfo BETWEEN
    (Last_Day(ADD_MONTHS(SYSDATE,-2))+1) AND ( Last_Day(ADD_MONTHS(SYSDATE,-1)))
    GROUP BY
    vw_cc_provider_businfo.ZONE,
    ccx_ersdispatchOffer.ext_dispatchOriginalETA


    THANKS VERY MUCH,
    Farshad
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    is purely illogical. What do u want you to do with that statement ?

    If you want to count the the recrods meeting that criteria, the query could be rewritten as

    Code (SQL):

    SUM(CASE WHEN (ccx_ersdispatch.ext_dispatchOriginalETA >= 0 AND ccx_ersdispatch.ext_dispatchOriginalETA <= 15)
            THEN 1 ELSE 0
           END) AS LM15
     
    or

    Code (SQL):

    SUM(CASE WHEN (ccx_ersdispatch.ext_dispatchOriginalETA BETWEEN  0 AND 15)
            THEN 1 ELSE 0
           END) AS LM15
     
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi check below Query... It may help you to give some idea..

    SELECT a.ZONE AS Zone,
    b.ext_dispatchOriginalETA ,
    SUM(
    CASE
    WHEN (b.ext_dispatchOriginalETA >= 0
    AND b.ext_dispatchOriginalETA <= 15)
    THEN (SELECT COUNT(b.ext_OfferAccepted)
    FROM vw_cc_provider_businfo a,
    ccx_ersdispatchOffer b
    WHERE vw_cc_provider_businfo = ccx_ersdispatch.ext_Providerid
    AND a.LOB = :LOB
    AND a.network = :Network
    AND a.ZONE = :Zone
    AND vw_cc_provider_businfo BETWEEN (Last_Day(ADD_MONTHS(SYSDATE,-2))+1) AND ( Last_Day(ADD_MONTHS(SYSDATE,-1)))
    )END) AS LM15
    FROM vw_cc_provider_businfo a,
    ccx_ersdispatchOffer b
    WHERE vw_cc_provider_businfo = ccx_ersdispatch.ext_Providerid
    AND a.LOB = :LOB
    AND a.network = :Network
    AND a.ZONE = :Zone
    AND vw_cc_provider_businfo BETWEEN (Last_Day(ADD_MONTHS(SYSDATE,-2))+1) AND ( Last_Day(ADD_MONTHS(SYSDATE,-1)))
    GROUP BY a.ZONE,
    b.ext_dispatchOriginalETA;