Problem in using group by statement in sql

I am not able to group by table which has all the values same but has two rows. I am not understanding what is the reason behind that.

After running this query i am getting the table below and i want to group the table along the LOT_No.

SELECT 
  c.LOT_NO LOT_NO
, (4-(DECODE(c.qty1,0,1,0) + DECODE(c.qty2,0,1,0) + DECODE(c.qty3,0,1,0) + DECODE(c.qty4,0,1,0))) NON_ZERO_QTY
, count(c.lot_no) over () as LOT_COUNT
FROM 
 CIM.QLDT_6001 a 
, CIM.PART_LOT_TBL b 
, CIM.JR_QTY_DETAIL_TBL c 
WHERE 
c.lot_no = 'F-AJR-111026-603'
AND 
a.val01 >= 100 
AND 
b.lot_no = c.lot_no
AND 
a.lot_no like '%' || b.old_lot_no || '%'

TABLE :

     LOT_NO       NON_ZERO_QTY  LOT_COUNT
F-AJR-111026-603      3             2
F-AJR-111026-603      3             2

Now when i am trying to Group by LOT_No, i am getting below error.

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:
Error at Line: 19 Column: 10

I also tried to take all columns from SELECT statement, but then i got this error

ORA-00904: "LOT_COUNT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 19 Column: 34

I am totally confused what is going on. Can anyone please help.

Answer

There are multiple issues in your query.

• group by c.lot_no is required
• Second expression in SELECT must be wrapped in SUM aggregate function. (change the expression of CASE statement according to requirement)
• Always use the standard ANSI-join. (Recommended)

Check the below solution:

SELECT 
  c.LOT_NO LOT_NO
, SUM(CASE WHEN (4-(DECODE(c.qty1,0,1,0) + DECODE(c.qty2,0,1,0) + DECODE(c.qty3,0,1,0) + DECODE(c.qty4,0,1,0))) > 0 
        THEN 1 ELSE 0 END) NON_ZERO_QTY
, count(c.lot_no) as LOT_COUNT
FROM 
 CIM.QLDT_6001 a 
JOIN CIM.PART_LOT_TBL b ON a.lot_no like '%' || b.old_lot_no || '%'
JOIN CIM.JR_QTY_DETAIL_TBL c ON b.lot_no = c.lot_no 
WHERE 
c.lot_no = 'F-AJR-111026-603'
AND a.val01 >= 100 
GROUP BY c.LOT_NO