How to select grouped column in one table and other column in different table with third table with foreign keys?



  • I am using my PostgreSQL and here is explanation what is wrong:

    1. I have first table river_changes and it has structure:
    id_hydro size date
    1 50 2002
    1 55 1992
    2 5 2005
    2 20 2022
    2 10 2016
    3 80 2018
    3 40 1999
    3 60 1987
    1. I have second table hydro_river:
    id_hydro id_river
    1 240
    2 12
    3 7
    4 34
    5 96
    6 43
    7 78
    8 85
    1. And the last one river:
    name id_river
    one 240
    two 12
    three 7
    four 34
    five 96
    fgh 43
    jgh 78
    pop 85

    So, i have my code:

    SELECT DISTINCT river.name, AVG(river_changes.size)
    FROM river, hydro, river_changes
    WHERE river_changes.id_hydro = hydro.id_hydro
         AND hydro.id_river = river.id_river;
    

    What is my problem? I do not know where i have to place "GROUP BY"

    I have to find avarage river_changes.size and find name for that river. I need it to show river's name and size through years in average.



  • The group by goes after the where clause. I believe you're looking for something like this.

    SELECT river.name, AVG(river_changes.size) AS AverageSize
    FROM river
        INNER JOIN hydro_river ON hydro_river.id_river = river.id_river
        INNER JOIN river_changes ON river_changes.id_hydro = hydro_river.id_hydro
    GROUP BY river.name;
    

    This is the output.

    name AverageSize
    one 278
    three 60
    two 11

    You can find the fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3f404da703cc676c6743b7a691001096 .




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2