horseatingweeds horseatingweeds - 1 year ago 70
SQL Question

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));

vkp vkp
Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download