Aggregate Query in SQL Developer

Question

enter image description here

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

• miracle173: when you get an error message you should add this to your question
• chels: @miracle173 Below is the error msg: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" *Cause: *Action: Error at Line: 1 Column: 91

Answer 1

Assume you have the following table TAB(name,value)

name value
a 2
a 3
a 2
b 4
b 5
b 5
c 6
c 6
c 6

what are the result sets of the following queries

query 1:

select name, sum(value)
from TAB
group by name
order by name

query 2:

select name, sum(distinct value)
from TAB
group by name
order by name

query 3:

select name, value
from TAB
group by name
order by name

• chels: qry 1 returns 3 rows with of each name. QRY 2 - returns 5 rows, A=2, B=2,C=1. QRY 3, Error msg. Just like the one I got.
• miracle173: and what do you think should query 3 return?
• chels: expectation, 3 rows. but error in the qry because it wants u to add another group by value, then the result will end up being qry 2.
• miracle173: "expectation, 3 rows." yes, because there are 3 groups (3 different names) but "value" does not make sense for group 'a' and 'b', because there are more than one value in a group. "Value" does not make sense on the group.. For the 'c' group it would make sense but Oracle does not allow this. It allows only columns that are in the group by clause or aggregate functions. workaround: add all the columns that you want to display to the group by clause. If each of these columns only takes one value on this group, the group isn't split in smaller groups and you get exactly what you want.
• miracle173: There are databases ( I think mysql) that allow columns that are not in the group by clause and select a random value of the group for this columns if there are more than one value in a group.

Answer 2

Since this was tagged for oracle-sql-developer, I'm guessing you are having problems using SQL-Developer for detecting and solving this common syntax problem.

SQL-Developer highlights potential syntax problems with a yellow squiggly line under the problematic syntax. SQL-Developer will use a magenta color squiggly line for major syntax errors. original SQL with yellow highlight for bad syntax

Your entire SELECT clause is yellow; you have a problem with your syntax related to your SELECT statement.

If you noticed, the tables (invoice, payments ) are also underlined with yellow squiggly line, that is because I don't have your tables.

The magenta squiggly line at the very end is indicating that I am missing a semi-colon.

Now that you have a visual clue, you can hover your cursor over the syntax and SQL-Developer will pop-up a window and make an edit suggestion.

pop-up window

It is not always correct. But, it should show you what is wrong with your syntax.

If you want, just click the blue text in the pop-up window and SQL-Developer will make the correction for you. For this example, you'll have to correct the correction.