Aggregate Query in SQL Developer

0 votes
2 answers
714 views
                          


Below is my query. i get an error msg. it works when i remove the fields without the sum. I have added a screen shot of the data. Thanks!    

    SELECT  
        S.PRODUCT_ID, 
        SUM(DISTINCT S.AWARD_AMT),
        SUM(DISTINCT S.EST_AMT),
        SUM(PMT.PMT_AMT),
        S.CREATE_DT,
        S.APPROVE_BY,
        S.EVALUATOR 
    FROM SALES S
        INNER JOIN INVOICE INV    
            ON  S.PRODUCT_ID = INVS.PRODUCT_ID
        INNER JOIN PAYMENTS PMT 
            ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID 
                AND INV.SEQ_NO = PMT.SEQ_NO
    WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' 
        AND INV.STATUS_CD = 'CREATE' 
    GROUP BY S.PRODUCT_ID
    order by S.PRODUCT_ID

This was my final query. It worked. I had to remove the dups in excel

    SELECT  
        S.PRODUCT_ID, 
        S.CREATE_DT,
        S.APPROVE_BY,
        S.EVALUATOR ,
        SUM(DISTINCT S.AWARD_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS S.AWARD_AMT,
        SUM(DISTINCT S.EST_AMT) OVER (PARTITION BY S.PRODUCT_ID) AS EST_COST_TOT,
         SUM(PMT.LINE_AM PMT.PMT_AMT T) OVER (PARTITION BY OM.O_OMO_NO) AS TOTAL_PMT
    FROM SALES S
        INNER JOIN INVOICE INV    
            ON  S.PRODUCT_ID = INVS.PRODUCT_ID
        INNER JOIN PAYMENTS PMT 
            ON INV.S.PRODUCT_ID = PMT.S.PRODUCT_ID 
                AND INV.SEQ_NO = PMT.SEQ_NO
    WHERE CAST(CREATE_DT AS DATE)>='01-JAN-2014' 
        AND INV.STATUS_CD = 'CREATE' and SUBSTR(APPROVE_BY,1,3) IN ('PEP','SMI', 'MOP','3/4')
    GROUP BY S.PRODUCT_ID
    order by S.PRODUCT_ID

ERROR msg:ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
Error at Line: 1 Column: 91


                        
Asked by chels (1 rep)
May 28, 2020, 01:57 AM
Last activity: Jun 29, 2025, 02:02 PM
Aggregate Query in SQL Developer

Related Questions
