Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: need help with group by function.

    hi,

    I need some urgent help for the query mentioned below:

    SELECT
    GEO_ROLLUP, PRODUCT_NM,
    DECODE
    ( TO_CHAR(FORECAST_DT, 'MON-YYYY'), 'OCT-2008', 1- (SUM ( ABS( ABS_DIFF))/ SUM (ABS( ORDERS_BOOKED_QTY)) ) ) DFA_OCT_08,
    DECODE
    ( TO_CHAR(FORECAST_DT, 'MON-YYYY'), 'NOV-2008', 1- (SUM ( ABS( ABS_DIFF))/ SUM (ABS( ORDERS_BOOKED_QTY)) ) ) DFA_NOV_08,
    DECODE
    ( TO_CHAR(FORECAST_DT, 'MON-YYYY'), 'DEC-2008', 1- (SUM ( ABS( ABS_DIFF))/ SUM (ABS( ORDERS_BOOKED_QTY)) ) ) DFA_DEC_08
    FROM GSPDBA.SOP_GLOBAL_DFA_VW
    WHERE Product_NM = 'PROPECIA 1 MG TABS' AND GEO_ROLLUP ='AP' AND (TO_CHAR(FORECAST_DT, 'MON-YYYY') IN ('OCT-2008', 'NOV-2008', 'DEC-2008')
    GROUP BY TO_CHAR(FORECAST_DT, 'MON-YYYY'), GEO_ROLLUP, PRODUCT_NM
    The output getting displayed is:
    geo_rollup product_nm dfa_oct_08 dfa_nov_08 dfa_dec_08
    AP PROPECIA 1 MG TABS NULL NULL 0.353832787
    A PPROPECIA 1 MG TABS NULL 0.464860037 NULL
    A PPROPECIA 1 MG TABS 0.570629747 NULL NULL


    however, the exxpected output should be:
    geo_rollupproduct_nmdfa_oct_08dfa_nov_08dfa_dec_08
    APPROPECIA 1 MG TABS0.5706297470.4648600370.353832787


    i have tried using GROUP BY FORECAST_DT, GEO_ROLLUP, PRODUCT_NM which also didnot solved the purpose..
    also, i tried using GROUP BY GEO_ROLLUP, PRODUCT_NM which is flagging some group by error.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool MAX() it

    Try MAX() function like this:
    Code:
      SELECT   Geo_rollup, Product_nm, MAX (Dfa_oct_08) Dfa_oct_08,
               MAX (Dfa_nov_08) Dfa_nov_08, MAX (Dfa_dec_08) Dfa_dec_08
        FROM   (  SELECT   Geo_rollup, Product_nm,
                           DECODE (TO_CHAR (Forecast_dt, 'MON-YYYY'), 'OCT-2008',
                           1 - (SUM(ABS(Abs_diff)) / SUM(ABS(Orders_booked_qty))))
                               Dfa_oct_08,
                           DECODE (TO_CHAR (Forecast_dt, 'MON-YYYY'), 'NOV-2008',
                           1 - (SUM(ABS(Abs_diff)) / SUM(ABS(Orders_booked_qty))))
                               Dfa_nov_08,
                           DECODE (TO_CHAR (Forecast_dt, 'MON-YYYY'), 'DEC-2008',
                           1 - (SUM(ABS(Abs_diff)) / SUM(ABS(Orders_booked_qty))))
                               Dfa_dec_08
                    FROM   Gspdba.Sop_global_dfa_vw
                   WHERE   Product_nm = 'PROPECIA 1 MG TABS' AND Geo_rollup = 'AP'
                           AND (TO_CHAR (Forecast_dt, 'MON-YYYY') IN
                                        ('OCT-2008', 'NOV-2008', 'DEC-2008'))
                GROUP BY   TO_CHAR (Forecast_dt, 'MON-YYYY'), Geo_rollup,
                           Product_nm)
    GROUP BY   Geo_rollup, Product_nm;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •