Re: for each row get derived data

Without reading too carefully: if you want a separate count for each row returned you need a GROUP BY for all of the non-group function columns in your query.

"Christopher Pisz" <nospam_at_somewhere.com> wrote in message news:JIyX8.79114$eF5.2506043_at_twister.austin.rr.com...
> Hiyas,
> I am having lots of trouble trying to figure out
> how to do this. I think I need "derived data" for
> each row returned. Here is instructions:
>
> many booking agents need know the flight number,
> flight date and number of seats avaialable. create a
> view for this.
>
> Seats Available is "derived data" :(
>
> well your going to have to know the tables and
> keys in my db so here are the creates I had.
>
> CREATE TABLE LOCATION(
> CITY_CODE NUMBER(3) PRIMARY KEY,
> CITY_NAME VARCHAR2(20) NOT NULL,
> STATE_CODE VARCHAR2(2) NOT NULL);
>
> CREATE TABLE AIRPORT(
> AIRPT_CODE VARCHAR2(3) PRIMARY KEY,
> AIRPT_NAME VARCHAR2(30) NOT NULL,
> CITY_CODE NUMBER(3),
> FOREIGN KEY (CITY_CODE) REFERENCES LOCATION);
>
> CREATE TABLE CUSTOMER(
> CUST_NUM NUMBER(4) PRIMARY KEY,
> CUST_NAME VARCHAR2(15) NOT NULL);
>
> CREATE TABLE AC_TYPE(
> AC_TYPE VARCHAR2(8) PRIMARY KEY,
> DESCRIPTION VARCHAR2(25) NOT NULL,
> CAPACITY NUMBER(3) NOT NULL,
> AC_RANGE NUMBER(4) NOT NULL);
>
> CREATE TABLE AIRCRAFT(
> SERIAL_NUM VARCHAR2(8) PRIMARY KEY,
> AC_TYPE VARCHAR2(8) NOT NULL,
> FOREIGN KEY (AC_TYPE) REFERENCES AC_TYPE);
>
> CREATE TABLE FLIGHT(
> FLT_NUM NUMBER(3) PRIMARY KEY,
> TOD NUMBER(4) NOT NULL,
> FROM_AIRPT VARCHAR2(3),
> TOA NUMBER(4) NOT NULL,
> TO_AIRPT VARCHAR2(3),
> FOREIGN KEY (FROM_AIRPT) REFERENCES AIRPORT,
> FOREIGN KEY (TO_AIRPT) REFERENCES AIRPORT);
>
> CREATE TABLE SCHEDULED_FLT(
> FLT_DATE DATE,
> FLT_NUM NUMBER(3),
> SERIAL_NUM VARCHAR2(8),
> PRIMARY KEY (FLT_DATE, FLT_NUM),
> FOREIGN KEY (FLT_NUM) REFERENCES FLIGHT,
> FOREIGN KEY (SERIAL_NUM) REFERENCES AIRCRAFT);
>
> CREATE TABLE RESERVATION(
> FLT_DATE DATE,
> FLT_NUM NUMBER(3),
> CUST_NUM NUMBER(4),
> PRIMARY KEY (FLT_DATE, FLT_NUM, CUST_NUM),
> FOREIGN KEY (FLT_DATE, FLT_NUM) REFERENCES SCHEDULED_FLT,
> FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER);
>
> I started with trying to get the seats available
>
> SELECT COUNT(R.CUST_NUM) - COUNT(T.CAPACITY) AS SEATS_AVAIL
> FROM RESERVATION R, SCHEDULED_FLT S, CUSTOMER C,
> AC_TYPE T, AIRCRAFT A
> WHERE R.CUST_NUM = C.CUST_NUM AND
> R.FLT_DATE = S.FLT_DATE AND
> R.FLT_NUM = S.FLT_NUM;
>
> and then added the other two items needed
>
> SELECT R.FLT_NUM, R.FLT_DATE, COUNT(R.CUST_NUM) - COUNT(T.CAPACITY) AS
> SEATS_AVAIL
> FROM RESERVATION R, SCHEDULED_FLT S, CUSTOMER C,
> AC_TYPE T, AIRCRAFT A
> WHERE R.CUST_NUM = C.CUST_NUM AND
> R.FLT_DATE = S.FLT_DATE AND
> R.FLT_NUM = S.FLT_NUM AND
> S.SERIAL_NUM = A.SERIAL_NUM AND
> A.AC_TYPE = T.AC_TYPE;
>
> but it returns an error:
> ERROR at line 1:
> ORA-00937: not a single-group group function
>
> What do I need to do? This is obviously homework, but note
> that I made an attempt on my own and put thought into it before
> asking for help. I can't find much help in my textbook.
> Thanx,
> Christopher
>
>
>
Received on Fri Jul 12 2002 - 13:32:17 CEST