Single-group group function error

Question

I'm new to SQL and am currently having an error as stated in the title for my sql.

This is my SQL query:

SELECT APPLICANT.APPNUM,APPLICANT.APPNAME,COUNT(APPLICATION.APPCNAPPNUM) AS "Total No. of Positions Applied"
FROM APPLICANT,APPLICATION
WHERE APPLICANT.APPNUM = APPLICATION.APPCNAPPNUM;

This is the Create statements:

CREATE TABLE    APPLICANT (
appNum      varchar2(10)    not null,
appName     varchar2(70),
appDOB      date,
appCity     varchar2(70),
appStreet       varchar2(70),
appHouseNum varchar2(10),
constraint applicant_Pkey primary key (appNum)
);

CREATE TABLE    APPLICATION (
appcnPosNum     varchar2(10)    not null,
appcnPosStOffrDt    date        not null,
appcnAppNum     varchar2(10)    not null,
appcnDt         date,
constraint application_Pkey primary key (appcnPosNum, appcnPosStOffrDt, appcnAppNum),
constraint application_Fkey1 foreign key (appcnPosNum, appcnPosStOffrDt) references
POSITION(posNum, posStartOfferDt),
constraint application_Fkey2 foreign key (appcnAppNum) references APPLICANT(appNum)
);

Can someone give me any suggestions as how to solve this error?

score 3 · Accepted Answer · answered Oct 30 '13 at 13:43

3

Use GROUP BY like:

SELECT APPLICANT.APPNUM,APPLICANT.APPNAME,COUNT(APPLICATION.APPCNAPPNUM) AS "Total No. of Positions Applied"
FROM APPLICANT,APPLICATION
WHERE APPLICANT.APPNUM = APPLICATION.APPCNAPPNUM
GROUP BY APPLICANT.APPNUM,APPLICANT.APPNAME

It's good to use also INNER JOIN instead of old school multiple tables, like:

SELECT APPLICANT.APPNUM,APPLICANT.APPNAME,COUNT(APPLICATION.APPCNAPPNUM) AS "Total No. of Positions Applied"
FROM APPLICANT
INNER JOIN APPLICATION
ON APPLICANT.APPNUM = APPLICATION.APPCNAPPNUM
GROUP BY APPLICANT.APPNUM,APPLICANT.APPNAME

answered Oct 30 '13 at 13:43

Edper

9,144
1
27
46

What's the difference between `INNER JOIN` and the old sch multiple tables? And for the `GROUP BY` clause, is it a must to use both columns? Can I just use one? Just asking ^^ – Joel Seah Oct 30 '13 at 13:50
It depends in your situation. For example if you GROUP BY State and City then you need it to group by STATE and CITY otherwise you could either group by STATE or CITY. See link [here](http://www.w3schools.com/sql/sql_groupby.asp). – Edper Oct 30 '13 at 13:59
As for the old school multiple tables vs INNER JOIN, the former is implicit by nature and could have unexpected results while the latter is explicit and therefore you are not in for untoward results. Otherwise they will be both the same actually. Here is a [link](http://stackoverflow.com/questions/5118562/inner-join-vs-multiple-table-names-in-from) for the discussion. – Edper Oct 30 '13 at 14:02

score 1 · Answer 2 · answered Oct 30 '13 at 13:43

Whenever you have an aggregate function (like COUNT) you need to include a GROUP BY clause in your query that contains all the non-aggregate fields from the "SELECT" projection. For this specific case, you need to do the following:

SELECT APPLICANT.APPNUM,APPLICANT.APPNAME,COUNT(APPLICATION.APPCNAPPNUM) AS "Total No. of     Positions Applied"
FROM APPLICANT,APPLICATION
WHERE APPLICANT.APPNUM = APPLICATION.APPCNAPPNUM
GROUP BY APPLICANT.APPNUM, APPLICANT.APPNAME;

Single-group group function error

2 Answers2