Divison with more than one result from postgresql query

Question

I am using postgresql and I have a table called accidents (state, total accidents) and another table called population. I want to get the top 3 state names with high total accidents and then get the population of those 3 states divided by total accidents in postgresql? How to write the query in the following way?

Explanation:
Population Table

rank|            state            | population 
 ---+-----------------------------+------------
  1 | Uttar Pradesh               |  199581477
  2 | Maharashtra                 |  112372972
  3 | Bihar                       |  103804630
  4 | West Bengal                 |   91347736
  5 | Madhya Pradesh              |   72597565
  6 | Tamil Nadu                  |   72138958
  7 | Rajasthan                   |   68621012
  8 | Karnataka                   |   61130704
  9 | Gujarat                     |   60383628
 10 | Andhra Pradesh              |   49665533
 11 | Odisha                      |   41947358
 12 | Telangana                   |   35193978
 13 | Kerala                      |   33387677
 14 | Jharkhand                   |   32966238
 15 | Assam                       |   31169272
 16 | Punjab                      |   27704236
 17 | Haryana                     |   25753081
 18 | Chhattisgarh                |   25540196
 19 | Jammu and Kashmir           |   12548926
 20 | Uttarakhand                 |   10116752
 21 | Himachal Pradesh            |    6856509
 22 | Tripura                     |    3671032
 23 | Meghalaya                   |    2964007
 24 | Manipur*β*                  |    2721756
 25 | Nagaland                    |    1980602
 26 | Goa                         |    1457723
 27 | Arunachal Pradesh           |    1382611
 28 | Mizoram                     |    1091014
 29 | Sikkim                      |     607688
 30 | Delhi                       |   16753235
 31 | Puducherry                  |    1244464
 32 | Chandigarh                  |    1054686
 33 | Andaman and Nicobar Islands |     379944
 34 | Dadra and Nagar Haveli      |     342853
 35 | Daman and Diu               |     242911
 36 | Lakshadweep                 |      64429 

accident table:

            state            | eqto8 | eqto10 | mrthn10 | ntknwn | total  
-----------------------------+-------+--------+---------+--------+--------
Andhra Pradesh              |  6425 |   8657 |    8144 |  19298 |  42524
Arunachal Pradesh           |    88 |     76 |      87 |      0 |    251
Assam                       |     0 |      0 |       0 |   6535 |   6535
Bihar                       |  2660 |   3938 |    3722 |      0 |  10320
Chhattisgarh                |  2888 |   7052 |    3571 |      0 |  13511
Goa                         |   616 |   1512 |    2184 |      0 |   4312
Gujarat                     |  4864 |   7864 |    7132 |   8089 |  27949
Haryana                     |  3365 |   2588 |    4112 |      0 |  10065
Himachal Pradesh            |   276 |    626 |     977 |   1020 |   2899
Jammu and Kashmir           |  1557 |    618 |     434 |   4100 |   6709
Jharkhand                   |  1128 |    701 |    1037 |   2845 |   5711
Karnataka                   | 11167 |  14715 |   18566 |      0 |  44448
Kerala                      |  5580 |  13271 |   17323 |      0 |  36174
Madhya Pradesh              | 15630 |  16226 |   19354 |      0 |  51210
Maharashtra                 |  4117 |   5350 |   10538 |  46311 |  66316
Manipur                     |   147 |    453 |     171 |      0 |    771
Meghalaya                   |   210 |    154 |     119 |      0 |    483
Mizoram                     |    27 |     58 |      25 |      0 |    110
Nagaland                    |    11 |     13 |      18 |      0 |     42
Odisha                      |  1881 |   3120 |    4284 |      0 |   9285
Punjab                      |  1378 |   2231 |    1825 |    907 |   6341
Rajasthan                   |  5534 |   5895 |    5475 |   6065 |  22969
Sikkim                      |     6 |    144 |       8 |      0 |    158
Tamil Nadu                  |  8424 |  18826 |   29871 |  10636 |  67757
Tripura                     |   290 |    376 |     222 |      0 |    888
Uttarakhand                 |   318 |    305 |     456 |    393 |   1472
Uttar Pradesh               |  8520 |  10457 |   10995 |      0 |  29972
West Bengal                 |  1494 |   1311 |     974 |   8511 |  12290
Andaman and Nicobar Islands |    18 |    104 |     114 |      0 |    236
Chandigarh                  |   112 |     39 |     210 |     58 |    419
Dadra and Nagar Haveli      |    40 |     20 |      17 |      8 |     85
Daman and Diu               |    11 |      6 |       8 |     25 |     50
Delhi                       |     0 |      0 |       0 |   6937 |   6937
Lakshadweep                 |     0 |      0 |       0 |      3 |      3
Puducherry                  |   154 |    668 |     359 |      0 |   1181
All India                   | 88936 | 127374 |  152332 | 121741 | 490383

So that result should be

21.57
81.03
107.44

explanation:

Highest accidents states Tamilnadu, Maharashtra, Madhyapradesh.

Tamilnadu population/accidents = 21213/983 = 21.57 (Assumed values)
Maharasthra population/accidents = 10000/123 = 81.03
Madhyapradesh population/accidents = 34812/324 = 107.44

My query is:

SELECT POPULATION/
            (SELECT TOTAL
            FROM accidents
            WHERE STATE NOT LIKE 'All %' 
            ORDER BY TOTAL DESC
            LIMIT 3)
    aVG FROM population 
    WHERE STATE IN  
           (SELECT STATE 
            FROM accidents
            WHERE STATE NOT LIKE 'All %' 
            ORDER BY TOTAL DESC 
            LIMIT 3);

throwing ERROR: more than one row returned by a subquery used as an expression.

How to modify the query to get the required result or any other way to get the result in postgresql?

Answer 1

This ought to do it.

SELECT a.state, population.population/a.total FROM

    (SELECT total, state FROM accidents WHERE state <> 'All India' ORDER BY total DESC LIMIT 3 ) AS a
    INNER JOIN population on a.state = population.state