CODEWITHSUNDEEP
sqloracle-databaseoracle11g
luongkhanh
luongkhanh

Reputation: 1823

How to fix wrong value when calculate sum two column in oracle

I''m using CTE in oracle to calculate value of two columns with 2 alias. and then i sum 2 alias. this is my table:

DELVRY_NO        ITM_NAME       TOTAL_QUANTITY      PACKING_STYLE    QUANTITY_IN_FULL_CTN
DVR20101900006   CHP-671R           61                   30                  60

this is my query:

WITH BOXCOUNT AS (                                                         
   SELECT ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX
 , ROUND(SUM(TOTAL_QUANTITY - QUANTITY_IN_FULL_CTN)) AS SPAREBOX 
FROM LOG0055D)
SELECT
    L55.DELVRY_NO
    , L55.ITM_NAME
    , L55.TOTAL_QUANTITY
    , L55.PACKING_STYLE
    , L55.QUANTITY_IN_FULL_CTN
    , BC.FULLBOX
    , BC.SPAREBOX
    ,SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX
 FROM LOG0055D L55, BOXCOUNT BC
 WHERE DELVRY_NO  = 'DVR20101900006'
 GROUP BY 
     L55.DELVRY_NO
     , L55.ITM_NAME
     , L55.TOTAL_QUANTITY
     , L55.PACKING_STYLE
     , L55.QUANTITY_IN_FULL_CTN
     , BC.FULLBOX
     , BC.SPAREBOX

when I run my query is has result following as:

DELVRY_NO       ITM_NAME  TOTAL_QUANTITY  PACKING_STYLE QUANTITY_IN_FULL_CTN  FULLBOX    SPAREBOX   TOTALBOX
DVR20101900006  CHP-671R      61               30            60               956          3077    4033

it seem 'FULLBOX' 'SPAREBOX' and 'TOTALBOX' is wrong value, exacly it will be:

FULLBOX   SPAREBOX   TOTALBOX
  2          1           3

How to fix them problem ? many thank

Upvotes: 0

Views: 185

Answers (1)

Littlefoot
Littlefoot

Reputation: 143033

To me, it looks as if the CTE misses delvry_no column (and a group by clause), while main query shouldn't use cross join but inner join by delvry_no. Something like this:

WITH
   boxcount
   AS
      (  SELECT delvry_no,
                ROUND (SUM (total_quantity / packing_style)) AS fullbox,
                ROUND (SUM (total_quantity - quantity_in_full_ctn)) AS sparebox
           FROM log0055d
       GROUP BY delvry_no)
  SELECT l55.delvry_no,
         l55.itm_name,
         l55.total_quantity,
         l55.packing_style,
         l55.quantity_in_full_ctn,
         bc.fullbox,
         bc.sparebox,
         SUM (bc.fullbox + bc.sparebox) AS totalbox
    FROM log0055d l55 JOIN boxcount bc ON bc.delvry_no = l55.delvry_no
   WHERE l555.delvry_no = 'DVR20101900006'
GROUP BY l55.delvry_no,
         l55.itm_name,
         l55.total_quantity,
         l55.packing_style,
         l55.quantity_in_full_ctn,
         bc.fullbox,
         bc.sparebox

Upvotes: 1

Related Questions