CODEWITHSUNDEEP
sqloracle-database
HiKaizer
HiKaizer

Reputation: 5

Update column based on aggregate function with sub query

I need to have a column with data taken from a count() function with a subquery. I am working with a temporary table at the moment and it is created by: 

CREATE TABLE tempfact1 AS
  (
    SELECT coursesloc.courseID, preferences.appid, campuses.campusid
    FROM coursesloc, preferences, campuses
  WHERE coursesloc.courseID = preferences.courseID AND coursesloc.campusid = campuses.campusid AND preferences.prefaccepted = 'Y'
  )
;

ALTER TABLE tempfact1
ADD TNA NUMERIC;

Then I run the following to try and generate the TNA (total number of applicants per campus) with the following:

UPDATE tempfact1
SET TNA = (SELECT COUNT(appid) FROM tempfact1 GROUP BY campusid)

Which I cannot do because it is returning more than one row. I'm unsure how to get it to only output one row at a time in the UPDATE, or if there is an easier and better way to just do this in the CREATE TABLE command.

Upvotes: 0

Views: 349

Answers (3)

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2715

Create the table as: 

 CREATE TABLE tempfact1 AS
      ( SELECT courseID,appid,campusid,COUNT(*) TNA 
        FROM(
        SELECT coursesloc.courseID, preferences.appid, campuses.campusid
        FROM coursesloc, preferences, campuses
         WHERE coursesloc.courseID = preferences.courseID AND coursesloc.campusi=campuses.campusid AND preferences.prefaccepted = 'Y' ) GROUP BY campusid
    );

Please correct if there are any syntax errors

Upvotes: 0

Anvesh
Anvesh

Reputation: 7693

I have created one small demo for your question...

/*Demo table....*/
create table mtp (rno integer,name varchar(max))

/*Demo Data....*/
insert into mtp values (1,'a')
insert into mtp values (1,'b')
insert into mtp values (2,'c')
insert into mtp values (2,'d')
insert into mtp values (2,'e')

/* Select Result, use this countid column for your new table...*/
select 
    COUNT(*) OVER (PARTITION BY rno)as countid
    ,rno
    ,name
From mtp

Upvotes: 2

Rohan
Rohan

Reputation: 2030

You may try by removing the GROUP BY clause. It will return multiple rows , i.e. count for each campus_id.

Upvotes: 0

Related Questions