CODEWITHSUNDEEP
sqloracle-databasefunctionaliasaggregate
David Passmore
David Passmore

Reputation: 6099

aggregate function alias error

I have the following SQL code:

SELECT  ACTIVITY_TYPE, COUNT(EVENT_ID) as etv, SUM(etv) as etva, AVG(etva)
FROM ACTIVITIES, "EVENTS"
WHERE ACTIVITIES.ACTIVITY_ID="EVENTS".ACTIVITY_ID
GROUP BY ACTIVITY_TYPE;

This is an attempt to answer this question:

d) A query that provides management information on take up of the various types of activities on offer. For each type of activity, the query should show the total number of individuals who took that type of activity and the average number of individuals taking each type of activity.

unfortunately i get the following error :

ORA-00904: "ETVA": invalid identifier

Any help would be great!

EDIT:

SELECT  ACTIVITY_TYPE, COUNT(EVENT_ID), AVG(COUNT(EVENT_ID))
FROM ACTIVITIES, "EVENTS"
WHERE ACTIVITIES.ACTIVITY_ID="EVENTS".ACTIVITY_ID
GROUP BY ACTIVITY_TYPE;

This is the new query i am now getting this error:

ORA-00937: not a single-group group function

TABLE STRUCTURE:

CREATE TABLE "GROUPS" (
  GROUP_ID NUMBER( 4 ) NOT NULL CONSTRAINT GROUP_ID_PK  PRIMARY KEY ,
  GROUP_NAME VARCHAR2(35) NULL,
  GROUP_NUMBER_OF_MEMBERS NUMBER( 3 ) NOT NULL ,
  GROUP_LEADER_ID NUMBER( 4 ) NOT NULL
);

CREATE TABLE MEMBERS (
  MEMBER_ID NUMBER( 4 ) NOT NULL CONSTRAINT MEMBER_ID_PK  PRIMARY KEY ,
  MEMBER_SURNAME VARCHAR2( 35 ) NOT NULL ,
  MEMBER_FIRST_NAME VARCHAR2( 35 ) NOT NULL ,
  MEMBER_DOB DATE NOT NULL ,
  MEMBER_GENDER VARCHAR2( 1 ) NOT NULL CONSTRAINT MEMBER_GENDER_CHECK CHECK ( MEMBER_GENDER IN ( 'M' , 'F' ) ),
  GROUP_ID NUMBER( 4 ) NOT NULL,
  CONSTRAINT GROUP_ID_FK_MEMBER FOREIGN KEY (GROUP_ID) REFERENCES "GROUPS"(GROUP_ID)
);

CREATE TABLE CHALETS (
  CHALET_NO NUMBER( 4 ) NOT NULL CONSTRAINT CHALET_ID_PK  PRIMARY KEY ,
  CHALET_NAME VARCHAR2( 35 ) NOT NULL,
  CHALET_CAPACITY NUMBER( 2 ) NOT NULL,
  CHALET_RATE NUMBER( 6 , 2 ) NOT NULL
);

CREATE TABLE SUPERVISORS (
  SUPERVISOR_ID NUMBER ( 4 ) NOT NULL CONSTRAINT SUPERVISOR_ID_PK PRIMARY KEY ,
  SUPERVISOR_SURNAME  VARCHAR2( 35 ) NOT NULL , 
  SUPERVISOR_FIRST_NAME VARCHAR2( 35 ) NOT NULL ,
  SUPERVISOR_MOBILE_NUMBER VARCHAR2( 11 ) NOT NULL
);

CREATE TABLE ACTIVITIES (
  ACTIVITY_ID NUMBER ( 4 ) NOT NULL CONSTRAINT ACTIVITY_ID_PK PRIMARY KEY , 
  ACTIVITY_TYPE VARCHAR ( 35 ) NOT NULL , 
  ACTIVITY_TITLE VARCHAR2( 35 ) NOT NULL
);

CREATE TABLE BOOKINGS (
  BOOKING_ID NUMBER( 4 ) NOT NULL CONSTRAINT BOOKING_ID_PK PRIMARY KEY , 
  GROUP_ID NUMBER( 4 ) NOT NULL , 
  CHALET_ID NUMBER( 4 ) NOT NULL ,
  BOOKING_START_DATE DATE NOT NULL , 
  BOOKING_END_DATE DATE NOT NULL ,
  CONSTRAINT GROUP_ID_FK_BOOKING FOREIGN KEY (GROUP_ID) REFERENCES GROUPS(GROUP_ID) ,
  CONSTRAINT CHALET_ID_FK_BOOKING FOREIGN KEY (CHALET_ID) REFERENCES CHALETS(CHALET_NO)
);

CREATE TABLE "EVENTS" (
  EVENT_ID NUMBER ( 4 ) NOT NULL CONSTRAINT EVENT_ID_PK PRIMARY KEY , 
  MEMBER_ID NUMBER ( 4 ) NOT NULL , 
  SUPERVISOR_ID NUMBER ( 4 ) NOT NULL ,
  ACTIVITY_ID NUMBER ( 4 ) NOT NULL ,
  EVENT_COMMENTS VARCHAR2( 500 ) ,
  EVENT_DATE DATE NOT NULL , 
  EVENT_TIME_SLOT VARCHAR2( 2 ) NOT NULL ,
  CONSTRAINT MEMBER_ID_FK_EVENT FOREIGN KEY (MEMBER_ID) REFERENCES MEMBERS(MEMBER_ID) ,
  CONSTRAINT SUPERVISOR_ID_FK_EVENT FOREIGN KEY (SUPERVISOR_ID) REFERENCES SUPERVISORS(SUPERVISOR_ID) ,
  CONSTRAINT ACTIVITY_ID_FK_EVENT FOREIGN KEY (ACTIVITY_ID) REFERENCES ACTIVITIES(ACTIVITY_ID)
);


ALTER TABLE "GROUPS" ADD CONSTRAINT MEMBER_ID_FK_GROUP_LEADER FOREIGN KEY (GROUP_LEADER_ID) REFERENCES MEMBERS(MEMBER_ID);

Upvotes: 0

Views: 4334

Answers (3)

Art
Art

Reputation: 5782

The etv and etva in your query are not columns but aliases. You cannot select them. To select them follow this and give meaningful aliases to your queries/columns:

SELECT ACTIVITY_TYPE, count_etv, sum_etva, AVG(sum_etva) avg_atva
  FROM
  (
   SELECT ACTIVITY_TYPE, count_etv, SUM(count_etv) as sum_etva
     FROM
   (
    SELECT ACTIVITY_TYPE, COUNT(EVENT_ID) as count_etv  
       --, SUM(etv) as etva, AVG(etva) -- NOT columns you cannot select them
     FROM ACTIVITIES, "EVENTS"
    WHERE ACTIVITIES.ACTIVITY_ID="EVENTS".ACTIVITY_ID
   GROUP BY ACTIVITY_TYPE
   )
   GROUP BY ACTIVITY_TYPE, count_etv
  )
 GROUP BY ACTIVITY_TYPE, count_etv, sum_etva
 /

Second query - cleaner, more readable...:

SELECT ACTIVITY_TYPE -- optional
      , total_events  -- optional
     , AVG(total_events) avg_events
  FROM
  (
   SELECT ACTIVITY_TYPE
        , COUNT(EVENT_ID) total_events
     FROM ACTIVITIES, "EVENTS"
    WHERE ACTIVITIES.ACTIVITY_ID="EVENTS".ACTIVITY_ID
    GROUP BY ACTIVITY_TYPE
   )
  GROUP BY ACTIVITY_TYPE, total_events
  /

Upvotes: 0

www
www

Reputation: 4391

As I understand from posted structure. Each Event is unique, in each Event participates only one Member. So you cannot count average number of Members per activity because is just sum of Events with particular activity. Only total number of participants is possible:

SELECT  ACTIVITY_TYPE, COUNT(EVENT_ID)
FROM ACTIVITIES, "EVENTS"
WHERE ACTIVITIES.ACTIVITY_ID="EVENTS".ACTIVITY_ID
GROUP BY ACTIVITY_TYPE;

Correct me if I am wrong.

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13334

The problem is with AVG(etva).

You cannot use alias in the same SELECT list where it was defined.

The bigger problem is that you cannot use aggregation function AVG over a value (SUM()).

Based on the task description you need AVG(etv) as it has been already pointed out above by codingbiz.

CONTINUE After the new query was posted.

My understanding is that EVENTS will have records connecting members with activities they participate in.

If so, you will need to JOIN EVENTS with sub-query that calculates counts for members from EVENTS and with 'ACTIVITIES' to just get Activity Titles.

Upvotes: 1

Related Questions