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!

Problem of GROUP BY

Discussion in 'SQL PL/SQL' started by RIAZ, May 31, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    hi all,
    I am working with following query to get the inventory variance. This query is working fine with "group by item" but i am unable to get the correct result with "group by category"

    Code (SQL):

    SELECT
    --ITEM_CODE
             category_description,
             ROUND (SUM (total_cost_prv), 2) total_cost_previous,
             ROUND (SUM (total_cost_cur), 2) total_cost_current,
             ROUND ((SUM (qty_cur) - SUM (qty_prv)) * SUM (unit_cost_prv),
                    2
                   ) qty_variance,
             ROUND ((SUM (unit_cost_cur) - SUM (unit_cost_prv)) * SUM (qty_cur),
                    2
                   ) price_variance
        FROM (SELECT msi.inventory_item_id AS item_id,
                        msi.segment1
                     || '-'
                     || msi.segment2
                     || '-'
                     || msi.segment3
                     || '-'
                     || msi.segment4 AS item_code,
                        mc.segment1
                     || '-'
                     || mc.segment2
                     || '-'
                     || mc.segment3
                     || '-'
                     || mc.segment4 AS category_id,
                     mc.description AS category_description,
                     msi.primary_unit_of_measure AS uom,
                     DECODE (cpic.pac_period_id,
                             :p_period_id_cur, ROUND
                                                 (NVL (cpic.total_layer_quantity,
                                                       0
                                                      ),
                                                  2
                                                 ),
                             0
                            ) qty_cur,
                     DECODE (cpic.pac_period_id,
                             :p_period_id_cur, ROUND (NVL (cpic.item_cost, 0), 2),
                             0
                            ) unit_cost_cur,
                     DECODE
                        (cpic.pac_period_id,
                         :p_period_id_cur, ROUND
                                         (  ROUND (NVL (cpic.item_cost, 0), 2)
                                          * ROUND
                                                 (NVL (cpic.total_layer_quantity,
                                                       0
                                                      ),
                                                  2
                                                 ),
                                          2
                                         ),
                         0
                        ) total_cost_cur,
                     DECODE (cpic.pac_period_id,
                             :p_period_id_prv, ROUND
                                                 (NVL (cpic.total_layer_quantity,
                                                       0
                                                      ),
                                                  2
                                                 ),
                             0
                            ) qty_prv,
                     DECODE (cpic.pac_period_id,
                             :p_period_id_prv, ROUND (NVL (cpic.item_cost, 0), 2),
                             0
                            ) unit_cost_prv,
                     DECODE
                        (cpic.pac_period_id,
                         :p_period_id_prv, ROUND
                                         (  ROUND (NVL (cpic.item_cost, 0), 2)
                                          * ROUND
                                                 (NVL (cpic.total_layer_quantity,
                                                       0
                                                      ),
                                                  2
                                                 ),
                                          2
                                         ),
                         0
                        ) total_cost_prv
                FROM mtl_item_categories mic,
                     mtl_categories mc,
                     mtl_system_items msi,
                     cst_pac_item_costs cpic
               WHERE cpic.pac_period_id BETWEEN :p_period_id_prv AND :p_period_id_cur
                 AND cpic.total_layer_quantity <> 0
                 AND cpic.cost_group_id = 1004
                 AND cpic.inventory_item_id = mic.inventory_item_id
                 AND mic.organization_id = 105
                 AND mic.category_set_id = 1
                 AND mc.category_id = mic.category_id
                 AND msi.inventory_item_id = mic.inventory_item_id
                 AND msi.organization_id = mic.organization_id)
    GROUP BY category_description
    ORDER BY category_d
     


    *Note if uncommitted ITEM_CODE then the same query is working fine with GROUP BY ITEM_CODE

    Any Suggestion ???
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    you need to add these other columns in the select statement in your GROUP By clause at the end.

    Code (Text):
     ROUND (SUM (total_cost_prv), 2) total_cost_previous,
             ROUND (SUM (total_cost_cur), 2) total_cost_current,
             ROUND ((SUM (qty_cur) - SUM (qty_prv)) * SUM (unit_cost_prv),
                    2
                   ) qty_variance,
             ROUND ((SUM (unit_cost_cur) - SUM (unit_cost_prv)) * SUM (qty_cur),
                    2
                   ) price_variance
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Thanks for your reply. I have done with another way.
    I used another SELECT statement above the two select and then grouped by category it is working fine.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi Riaz,
    Too many sub queries might lead to perfomance issue at later stage.
    This should work with category_id also provided all the relevant filed specified in Select come in the group by clause also.

    @ Tyro,

    Adding the other columns mentioned in the ROUND(SUM()) will give undesired output.


    Code (SQL):

    SQL> SELECT deptno,   round(SUM(sal)) rndsal
      2  FROM emp
      3  GROUP BY deptno;

        DEPTNO     RNDSAL
    ---------- ----------
            30       9400
            20      10875
            50       1900
            10       8750

    SQL> SELECT deptno,   round(SUM(sal)) rndsal
      2  FROM emp
      3  GROUP BY deptno,sal;

        DEPTNO     RNDSAL
    ---------- ----------
            10       5000
            50       1900
            20        800
            20       2975
            10       2450
            30       2500
            20       6000
            30        950
            20       1100
            10       1300
            30       1600
            30       2850
            30       1500

    13 ROWS selected.

    SQL>
     
    I think the the query can be made with minimum sub queries.