CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5236

Aggregate function calls cannot be nested?

In PostgreSQL database I have table called answers. This table stores information about how users answered a questions. There are only 4 question in the table. At the same time, the number of users who answered the questions can be dynamic and the user can answer only part of the questions.

Table answers:

| EMPLOYEE | QUESTION_ID | QUESTION_TEXT          | OPTION_ID | OPTION_TEXT  |
|----------|-------------|------------------------|-----------|--------------|
| Bob      | 1           | Do you like soup?      | 1         | Yes          |
| Alex     | 1           | Do you like soup?      | 2         | No           |
| Kate     | 1           | Do you like soup?      | 3         | I don't know |
| Bob      | 2           | Do you like ice cream? | 1         | Yes          |
| Alex     | 2           | Do you like ice cream? | 3         | I don't know |
| Oliver   | 2           | Do you like ice cream? | 1         | Yes          |
| Bob      | 3           | Do you like summer?    | 2         | No           |
| Alex     | 3           | Do you like summer?    | 1         | Yes          | 
| Jack     | 3           | Do you like summer?    | 2         | No           |
| Bob      | 4           | Do you like winter?    | 3         | I don't know |
| Alex     | 4           | Do you like winter?    | 1         | Yes          |
| Oliver   | 4           | Do you like winter?    | 3         | I don't know |

For example, with next code I can find average of the answers for question 1 and 2 of each person who answered for these questions.

select
    employee,
    avg(
        case when question_id in (1, 2) then option_id else null end
    ) as average_score
from
    answers
group by
    employee

Result:

| EMPLOYEE | AVERAGE_SCORE |
|----------|---------------|
| Bob      | 2             |
| Alex     | 2,5           |
| Kate     | 3             |
| Oliver   | 1             |

Now, I want to know the number of users whose average of the answers for question 1 and 2 is >= than 2. I tried next code but it raise error:

select
    count(
        avg(
            case when question_id in (1, 2) then option_id else null end
        )
    ) as average_score
from
    answers
where
    average_score >= 2
group by
    answers.employee

ERROR:

SQL Error [42803]: ERROR: aggregate function calls cannot be nested

Upvotes: 0

Views: 3383

Answers (2)

Amrita Srivastava
Amrita Srivastava

Reputation: 384

you have to use having clause.. it can be done simply as

select employee, [Average Score] = avg(case when question_id in (1, 2) 
                                            then option_id else null 
                                            end
                                       ) 
from answers group by employee having average_score > 2;

update : It must work now...

select employee, average_score = avg(case when question_id in (1, 2) 
                                            then option_id else null 
                                            end
                                       ) 
from answers group by employee having average_score > 2;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to filter after aggregation. That uses a having clause. In Postgres, you can also use filter:

select employee,
       avg(option_id) filter (where question_id in (1, 2)) as average_score
from answers
group by employee
having avg(option_id) filter (where question_id in (1, 2)) > 2;

If you want the count, then use this as a subquery: select count(*) from <the above query>.

It is strange that you are equating "option_id" with "score", but that is how your question is phrased.

Upvotes: 1

Related Questions