Most donor to acceptor ratio

Question

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. The donor sum amount is the total amount of blood, regardless of blood group, that people are ready to donate. The acceptor sum amount is the total amount of blood needed by that city.

There must be exactly two rows that denote the best and the worst ratios. The order of the row does not matter. Each row must contain the following attributes: The say name (CITY). The ratio (donor sum amount/acceptor sum amount ), correct to 4 decimal places.

the schema tables is given below:

These are the two tables

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.

Please see [ask] for more information on how to write a *specific programming question*. Stack Overflow is not the place for homework questions, and these questions often get closed without answers. Why would teachers/professors assign homework for it to be done by other people? — TheFungusAmongUs, May 14 '22 at 05:49
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, May 14 '22 at 06:07
'it's telling me I have some manual error ' Please add full error message, sample data and expected output as text, — P.Salmon, May 14 '22 at 07:08
Does this answer your question? [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — P.Salmon, May 14 '22 at 07:14

score 0 · Answer 1 · answered May 14 '22 at 07:13

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

score 0 · Answer 2 · edited May 15 '22 at 05:38

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

Most donor to acceptor ratio

2 Answers2