SUM & GROUP BY issue
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Harphajan_Singh
Posted On:   Sunday, August 11, 2002 07:37 PM

Hi there ! I CANNOT execute SUM function & GROUP BY function in my SQL ? Each Time I include say SUM(HBR_BIN1) I get the following error :- Execution Failed because ORA-00937: not a single-group group function Each time I do GROUP BY ( MACHINE_TEST_END_TIME .... ) I get this error :- Execution Failed because ORA-00979: not a GROUP BY expression My JSP is as follows :- Please, please offer some help guys ! myQuery1 = "select DISTINCT LOT_LOT_NUMBER, LOT_TEST_END_TIME,LOT_TEST_START_TIME, LOT_PROGRAM_NAME, LOT_PROGRAM_VERSION, LOT_DEVC_NUMBER, LOT_LOT_NUMBER, LOT_MASK_SET, LOT_TEST   More>>

Hi there !

I CANNOT execute SUM function & GROUP BY function in my SQL ?


Each Time I include say SUM(HBR_BIN1) I get the following error :-

			
				
Execution Failed because ORA-00937: not a single-group group function



Each time I do GROUP BY ( MACHINE_TEST_END_TIME .... ) I get this error :-


			
Execution Failed because ORA-00979: not a GROUP BY expression


My JSP is as follows :-


Please, please offer some help guys !

			
myQuery1 = "select DISTINCT LOT_LOT_NUMBER, LOT_TEST_END_TIME,LOT_TEST_START_TIME, LOT_PROGRAM_NAME, LOT_PROGRAM_VERSION, LOT_DEVC_NUMBER, LOT_LOT_NUMBER, LOT_MASK_SET, LOT_TEST_MODE, LOT_LOT_SIZE, LOT_TEMPERATURE, LOT_OPERATOR_ID, LOT_TOTAL_INSERTION, LOT_PROCESS_STEP, LOT_RUN_MODE, MACHINE_TEST_HEAD, MACHINE_SYSTEM_ID, MACHINE_TESTER_TYPE, SUM(HBR_BIN1),SUM(HBR_BIN2) from TRDB_LOT, TRDB_MACHINE, TRDB_HBR where (LOT_TEST_END_TIME=MACHINE_TEST_END_TIME AND LOT_LOT_NUMBER=MACHINE_LOT_NUMBER AND LOT_TEST_END_TIME=HBR_TEST_END_TIME AND LOT_LOT_NUMBER=HBR_LOT_NUMBER AND MACHINE_TEST_HEAD=HBR_TEST_HEAD AND MACHINE_DUT_SITE=HBR_DUT_SITE) AND LOT_LOT_NUMBER like '%"+lot_no+"%' AND (NOT MACHINE_TEST_HEAD LIKE '0') GROUP BY LOT_TEST_END_TIME, LOT_TEST_START_TIME, LOT_PROGRAM_NAME, LOT_PROGRAM_VERSION, LOT_DEVC_NUMBER, LOT_LOT_NUMBER, MACHINE_SYSTEM_ID, MACHINE_TESTER_TYPE, LOT_MASK_SET, LOT_TEST_MODE, LOT_LOT_SIZE, LOT_TEMPERATURE, LOT_OPERATOR_ID, MACHINE_TEST_HEAD " ;

try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@klmsph1:1521: klmpmis","name","passwd");
PreparedStatement prepstmt = conn.prepareStatement(myQuery1);

ResultSet outerRset = prepstmt.executeQuery();

while (rs.next())
{
// Get stuffs
}
}
   <<Less

Re: SUM & GROUP BY issue

Posted By:   Jeanne_Boyarsky  
Posted On:   Monday, August 12, 2002 06:00 PM

You need to group by all the fields other than the ones you are summing.

For example, using the table:

f1 | f2 | f3
----------------
f | b | 1
f | c | 3
h | d | 5

For the query:

select f1, f2, sum(f3)
from table
group by f1

In this case, you would be missing f2 in the group by. SQL is trying to display both "b" and "c" in one row for f2, along with 4 for the sum. As this is impossible, it generates an error.
About | Sitemap | Contact