CODEWITHSUNDEEP
sqlpostgresql
Abram
Abram

Reputation: 41844

Averaging multiple columns inside an SQL table join

The below code is working for me to create a table as below:

SELECT reviews.style_id, AVG("col1"), AVG("col2")
  FROM reviews, audios
 WHERE reviews.consumer_id = audios.consumer_id
 GROUP BY style_id

enter image description here

So my issue is that I would like to average col1 and col2, not worrying about NULL values, as they won't exist in my table. However, I do need to maintain the logic of the join. Thanks!

Upvotes: 0

Views: 1483

Answers (2)

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

As an alternative to the Accepted Answer, and based on OP's clarification in comments:

SELECT reviews.style_id, 
       Avg((col1+col2)*0.5) 
FROM   reviews 
       INNER JOIN audios 
         ON reviews.consumer_id = audios.consumer_id 
GROUP  BY reviews.style_id

This averaging of row-by-row averages of col1 and col2 will give the same results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Is this what you want?

SELECT reviews.style_id,
       (AVG("col1") + AVG("col2")) / 2.0
FROM reviews, audios
WHERE reviews.consumer_id = audios.consumer_id
GROUP BY style_id

Upvotes: 1

Related Questions