There is a blood bank which maintains two tables: DONOR that contains information on the people who are willing to donate blood and ACCEPTOR, the people who are in need of blood. The bank wants to conduct a survey and find out the city that has the best and the worst donor sum amount/acceptor sum amount ratio. Both ratios are unique. That is, exactly one city has the best ratio and exactly one city has the worst ratio.

select city, max(d.'abc') from(select CITY, (sum (a. 'amount')/sum (b. amount')) abc from 'donor' a join 'acceptor' b on a.'city'=b.'city' group by a.'city')d

Here is my approach, it's telling me I have some manual error and I can't figure it out.

CodePudding user response：

Your errors include using single quotes when you should be using backticks and sum (b. amount') - amount has closing quote(which should be a backtick) but no opening backtick. NB you don't need backticks at all unless identifiers are reserved words. When to use single quotes, double quotes, and backticks in MySQL

CodePudding user response：

Tried this query, it's working fine. However, I still feel there can be a shorter answer to this.

select * from (select *, row_number() over (order by abc) as rn from (select a.city, (sum(a.amount)/sum(b. amount)) abc from donor as a join acc as b on a.city=b.city group by a.city)d)e where e.rn=1 union (select * from (select *, row_number() over (order by abc desc) as rn from (select a.city, (sum(a.amount)/sum(b. amount)) abc from donor as a join acc as b on a.city=b.city group by a.city)d)e where e.rn=1);