SQL*Plus Report Problem (SQL Statement)

Hi Experts,
I am working on a project for my online class and am struggling with a SQL*Plus rpeort. It should be simple, but I don't understand what I am doing wrong.

We have been asked to import a .csv file with 15 fields into an oracle database. I ran SQL*Loader and inserted the records in a table called "employee_personal" We are to write a report in SQL*Plus, as script file (.sql), that shows the native country, occupation and occupation count by country. The end result would look something like:

native_country                 occupation                      count
Vietnam                   Adm-clerical                            12
                               Craft-repair                               23
                               Exec-managerial                       344

Yugoslavia             Craft-repair                              
                               Exec-managerial
                               Machine-op-inspct
                               Other-service
                               Priv-house-serv
I am able to get the "native_country" and "occupation" with the attached code, but I cannot figure oout how to make the COUNT() work. I have tried something like this:

SQL> select distinct native_country, occupation, COUNT(occupation) AS counter  from employee_personal
l
  2  ORDER BY native_country, occupation;
select distinct native_country, occupation, COUNT(occupation) AS counter  from employee_personal
                *
ERROR at line 1:
ORA-00937: not a single-group group function

I get the above error.
Will I have to split this one table to make this work?

I have also attached the .csv file, but I had to save it as .xls so it would upload..

Thanks in advance,

Kevin



Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

rem Occupations Report
 
set headsep !
 
ttitle 'Occupations by Country'
 
column native_country format a30
column occupation format a30
column counter format 999999.99
 
 
break on native_country skip 1 on report
 
set linesize 80
set pagesize 60
set newpage 0
set feedback off
 
spool activity.lst
 
select distinct native_country, occupation  from employee_personal
ORDER BY native_country, occupation;
 
 
spool off