CODEWITHSUNDEEP
sqlpostgresql
user6754289
user6754289

Reputation: 437

PostgreSQL won't do inner join with primary key

I have a query: 

SELECT id, year, avg(amount) from table1
WHERE amount IS NOT NULL
INNER JOIN table2
ON table1.id = table2.id
GROUP BY id

2 Issues:

  1. This only works if I remove the Where amount is not null, and
  2. I remove the 'id' from SELECT and GROUP BY

I can group by year, but It won't let me put the primary key "id" in my select statement and also group on it. Any suggestions?

In general, I'd like to get the avg amount for each id for each year and then join it with another table with id, but I want id in my returned query.

Upvotes: 0

Views: 727

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The correct syntax is:

SELECT id, year, avg(amount) 
FROM table1 JOIN
     table2
     ON table1.id = table2.id
WHERE amount IS NOT NULL
GROUP BY id, year;

WHERE is a clause that follows the FROM clause. JOIN is an operator that works only in the FROM clause. You will note that when I indent queries, the clauses are left justified, and the binary operators are at the end of line.

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

SELECT id, year,  avg(amount) over(partition by year, order by id) from table1
INNER JOIN table2
ON table1.id = table2.id
WHERE amount IS NOT NULL
GROUP BY id, year

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You should include also the where condition in ON clause 

  SELECT id, year, avg(amount) from table1

  INNER JOIN table2
  ON table1.id = table2.id and  amount IS NOT NULL
  GROUP BY id, year

and add year to group by too

Upvotes: 0

JNevill
JNevill

Reputation: 50064

You've got a lot of things going haywire here. Properly written your query should look something like:

SELECT table1.id, table1.year, avg(table2.amount) 
FROM table1
    INNER JOIN table2
        ON table1.id = table2.id
WHERE amount IS NOT NULL
GROUP BY table1.id, year;
  1. WHERE clause is separate from the FROM clause. They don't get combined.
  2. Your fields have their full path with the table name so your DB knows which id you want in the output
  3. All non-aggregated fields are present in your GROUP BY. Only older version of MySQL allow you to leave them out, and even then it produces nonsense and confusion.

Upvotes: 1

Twelfth
Twelfth

Reputation: 7180

You need to qualify the 'id' column as it is present in 2 tables in your query.

SELECT table1.id, year, avg(amount) from table1
INNER JOIN table2
ON table1.id = table2.id
WHERE amount IS NOT NULL
GROUP BY table1.id, year

Upvotes: 1

Related Questions