How to calculate avg of elements which

Question

I'm working with postgres

I have a table (tbl) with the following fields: name, words the fields type are strings)

I need to calculate for each name (which is aaa) the average percentage of words which are not null

I have tried something like:

SELECT AVG(COUNT(words is not null) - count(*))
FROM tbl
WHERE name="test"

But I got the following error:

aggregate function calls cannot be nested

I tried to change the query to:

SELECT (AVG((SELECT COUNT(*) FROM tbl WHERE words IS NOT NULL) - (SELECT COUNT(*) FROM tbl))
FROM tbl
WHERE name="test" 

but it seems that I got wrong values.

For example, for the following table:

name,   words 
----------------------------
test    abc test, 1, 2, 3
t2      NULL
test    NULL
t3      NULL
t2      a,b,c,d,e
test    def zxy
t2      NULL

the result for test need to be 2/3 (because there are 2 results of test which are not null and there are 3 test in the table)

How can I write the query in the right way ?

Answer 1

You cant nest it,

so go with subquery:

SELECT  
1.0*sum((case when a.words is null then 0 else 1 end))/
(SELECT  count(*) FROM tbl b WHERE b.name= a.name group by b.name)
FROM tbl a
WHERE a.name= 'test'
group by name 

Output:

Avg_
0.666666666666

Answer 2

I found the solution:

SELECT CAST((CAST((SELECT COUNT(*)
FROM tbl
WHERE name="test" and words IS NOT NULL) as float)/(SELECT COUNT(*)FROM tbl WHERE name="test")) as float) as result
FROM tbl
WHERE name="test"
GROUP BY Country