How to solve this query Using joins instead of group by
<p>I make below query I get my desired result if I skip group by clause 
but when I use group by clause one record is missing please help how to resolve this issue I am using SUM &amp; COUNT function so group by is necessary if you have a better way then I will appreciate your effort  , if there is a way use joins instead of group by then i think it will also help full</p>

<pre><code>SELECT                                                      
        P  .LOT_NO,
           P.REPCK_LOT,
           D.ORD_NO || '-' || D.VERSION AS ORD_NO,
           CUST.CUSTOMER_NAME,
           PRD_QTLY.QUALITY_DESC,
           LK.LOOKUP_VALUE BLEND,
           LK1.LOOKUP_ATTRIBUTE1 PRC,
           COUNT (ROLL_NO) ROLL_NO,
           SUM (D.LENGTH) TRANS_QTY,
           P.SR_NO,
           IN_LT_D.PURPOSE_ID,
           IN_LT_D.PROC_ID,
           IN_LT_D.MATCHING,
           D.INVEN_DATE,
           P.PACK_TYPE,
           P.LOT_YEAR,
           P.UM,
           IN_LT_D.WIDTH_IN AS FWIDTH,
           IN_LT_D.WIDTH_CM,
           LM.LOOM_ABB
    FROM   INVEN_AREA_DTL D,            
           INS_LTR_M IN_LT_M,
           INS_LTR_D IN_LT_D,
           OFA_CUSTOMERS_MV CUST,
           JOC_COMMON_LOOKUPS LK,
           JOC_COMMON_LOOKUPS LK1,
           JOC_PROD_QUALITY PRD_QTLY,
           JOC_LOOM_TYPE LM,
           JOC_LOT_ISSUE_REG LT,
           (SELECT   DISTINCT PD.UM,
                              P.P_DATE,
                              P.P_NO,
                              P.P_TYPE,
                              P.PACK_TYPE,
                              P.SR_NO,
                              P.DEPT_CODE,
                              P.BLND_CODE,
                              P.LOT_NO,
                              P.ORD_NO,
                              P.LOT_YEAR,
                              P.REPCK_LOT
              FROM   PACK P, PACK_DTL PD
             WHERE       P.P_TYPE = PD.P_TYPE
                     AND P.P_DATE = PD.P_DATE
                     AND P.P_NO = PD.P_NO
                     AND P.DEPT_CODE = PD.DEPT_CODE
                     AND P.BLND_CODE = PD.BLND_CODE
                     AND P.ORD_NO = NVL (:ORD, P.ORD_NO)
                     AND P.VERSION = NVL (:VER, P.VERSION)
                     AND P.LOT_NO = NVL (:LOT, P.LOT_NO)) P
   WHERE       IN_LT_M.ORDER_NO = D.ORD_NO
           AND IN_LT_M.VERSION = D.VERSION
           AND IN_LT_M.INS_NO = IN_LT_D.INS_NO
           AND IN_LT_D.SR_NO = D.SR_NO
           AND LK.LOOKUP_ID = IN_LT_D.BLEND_ID
           AND LK1.LOOKUP_ID = IN_LT_D.PURPOSE_ID
           AND LK1.LOOKUP_TYPE = 'END_PROCESS'
           AND CUST.CUSTOMER_ID = IN_LT_M.CUSTOMER_ID
           AND PRD_QTLY.QUALITY_ID = IN_LT_D.QUALITY_ID
           AND D.RET_DATE &gt;= :FDT
           AND D.RET_DATE &lt;= :TDT
           AND D.RECEIVE_DATE IS NOT NULL
           AND D.RECEIVE_STATUS = 'Y'
           AND D.RET_STS IS NOT NULL
           AND P.ORD_NO = D.ORD_NO
           AND P.SR_NO = D.SR_NO
           AND P.P_NO = D.ROLL_NO
           AND P.LOT_NO = D.LOT_NO
           AND P.LOT_YEAR = D.LOT_YEAR
           AND P.LOT_NO = LT.LOT_NO
           AND P.LOT_YEAR = LT.LOT_YEAR
           AND LT.LOOM_ID = LM.LOOM_ID
GROUP BY   D.ORD_NO || '-' || D.VERSION,
           P.LOT_NO,
           P.REPCK_LOT,
           CUST.CUSTOMER_NAME,
           P.SR_NO,
           ROLL_NO,
           D.LENGTH,
           LK.LOOKUP_VALUE,
           PRD_QTLY.QUALITY_DESC,
           IN_LT_D.PURPOSE_ID,
           LK1.LOOKUP_ATTRIBUTE1,
           IN_LT_D.PROC_ID,
           IN_LT_D.MATCHING,
           D.INVEN_DATE,
           P.PACK_TYPE,
           P.LOT_YEAR,
           P.UM,
           IN_LT_D.WIDTH_IN,
           IN_LT_D.WIDTH_CM,
           LM.LOOM_ABB
ORDER BY   D.INVEN_DATE, D.ORD_NO || '-' || D.VERSION, P.LOT_NO
</code></pre>