CODEWITHSUNDEEP
postgresqlaverage
Curcuma_
Curcuma_

Reputation: 901

Why am I getting a false average when applied on temporary table column

I'm trying to get the average of words based on each message.body count of words from messages table

an example of that would be

**message.body**
-------------------
-->"aaz aae aar"
-->"aaz"
-->"aaz aae"

Output must be: AVG( 3 + 1 + 2 ) = 2

For that I've been applying the following query 

SELECT AVG(temp.words) FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words FROM messages) AS temp

message.body is just text.

Any help will be appreciated.

Upvotes: 1

Views: 42

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51456

giving the result you expect:

t=# with messages(body) as (values('aaz aae aar'),('aaz'),('aaz aae')) SELECT AVG(temp.words) FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words FROM messages) AS temp;
        avg
--------------------
 2.0000000000000000
(1 row)

t=# with messages(body) as (values('aaz aae aar'),('aaz'),('aaz aae')) SELECT *FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words,messages.body FROM messages) AS temp;
 words |    body
-------+-------------
     3 | aaz aae aar
     1 | aaz
     2 | aaz aae
(3 rows)

Upvotes: 1

Curcuma_
Curcuma_

Reputation: 901

I'm answering my question: it happens that average function in postgres only accepts Floats as an argument, for that someone needs to cast the input before. Like this:

SELECT(AVG (temporary_.words)) AS average_amount FROM (SELECT CAST(array_length(string_to_array(messages.body,' '),1)  AS FLOAT) AS words FROM messages WHERE body!='' ) AS temporary_

Upvotes: 0

Related Questions