CODEWITHSUNDEEP
sqlpostgresql
Hommer Smith
Hommer Smith

Reputation: 27852

Using attribute in subquery for comparison

I have the following query that basically tells me the status of a user related to the exams he has done:

SELECT users.id, 
CASE WHEN users.closed_at IS NOT NULL 
       THEN 'closed'
     WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = COUNT(exams.id)
       THEN 'completed'
     WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = 0
       then 'not-completed'
     ELSE 'in-progress' 
END AS user_status
LEFT JOIN exams ON exams.user_id = users.id

This works fine, but now I also want to be able to filter depending on the exam status of the user, taking into account that I should be able to filter by multiple statuses. I have been suggested to do this with a subquery, so assuming I want to find the users which exam status is closed or 'not-completed', I have tried this:

SELECT users.id, 
    CASE WHEN users.closed_at IS NOT NULL 
           THEN 'closed'
         WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = COUNT(exams.id)
           THEN 'completed'
         WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = 0
           then 'not-completed'
         ELSE 'in-progress' 
    END AS user_status
    LEFT JOIN exams ON exams.user_id = users.id
    WHERE (
      SELECT 
       CASE WHEN users.closed_at IS NOT NULL 
           THEN 'closed'
         WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = COUNT(exams.id)
           THEN 'completed'
         WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = 0
           then 'not-completed'
         ELSE 'in-progress' 
         END AS user_status_in_inner_query
    ) user_status_in_inner_query = 'closed' OR user_status_in_inner_query = 'not-completed'

But it throws an error saying 'aggregate functions are not allowed in WHERE'. How can I do this?

Upvotes: 0

Views: 932

Answers (2)

user330315
user330315

Reputation:

Don't repeat the query in the WHERE clause, put everything into a derived table, then you can use the alias:

select *
from (
  SELECT users.id, 
         CASE 
            WHEN users.closed_at IS NOT NULL THEN 'closed'
            WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = COUNT(exams.id)
              THEN 'completed'
            WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = 0
              then 'not-completed'
            ELSE 'in-progress' 
         END AS user_status
  FROM users --<<< Seems to be missing in your query
     LEFT JOIN exams ON exams.user_id = users.id
) t
where user_status in ('closed', 'not-completed');

Upvotes: 1

404
404

Reputation: 8572

I expect something like this will work?

SELECT id, user_status
FROM (
        SELECT users.id, 
        CASE WHEN users.closed_at IS NOT NULL 
               THEN 'closed'
             WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = COUNT(exams.id)
               THEN 'completed'
             WHEN COUNT(exams.id) FILTER(users.closed_at IS NOT NULL OR requests.finished_at IS NOT NULL) = 0
               then 'not-completed'
             ELSE 'in-progress' 
        END AS user_status
        LEFT JOIN exams ON exams.user_id = users.id
) xx
WHERE user_status IN ('closed', 'not-completed')

Basically just take your original query as the subquery, and do a standard select on that with whatever filters you want.

Upvotes: 1

Related Questions