CODEWITHSUNDEEP
sqloracle-database
BenW
BenW

Reputation: 959

Oracle. Multiplying a sum() with a subquery.

Why does this query not work? I get the error "not a single-group group function". 

select sum(stock.stkprice) * (select nation.exchrate from nation where nation.natcode = 'UK') from stock;

Is it because the subquery might return multiple values? It's using the primary key, natcode, so it should only return a single exchange rate. Below are the tables. 

create table nation
(natcode char(3) not null,
natname char(20),
exchrate number(9,5),
constraint pk_nation primary key (natcode));

create table stock
(stkcode char(3) not null,
stkfirm char(20),
stkprice number(6,2),
stkqty number(8),
stkdiv number(5,2),
stkpe number(5),
natcode char(3),
constraint pk_stock primary key (stkcode),
constraint fk_hasnation foreign key (natcode) references nation(natcode));

Upvotes: 0

Views: 664

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Join the tables as the columns to be used for aggregation come from different tables. The query in question is incorrect syntactically.

select sum(stock.stkprice * nation.exchrate)
from nation 
join stock on stock.natcode=nation.natcode
where nation.natcode = 'UK'

Upvotes: 2

Related Questions