CODEWITHSUNDEEP
postgresql
user2810895
user2810895

Reputation: 1364

Postgresql query: schema does not exist

I've a problem with my PostgreSQL query. I'm a beginner so maybe it's a dumb question:

I need to find the client that has been the most to the moon. I tried to find the solution with a subquery but I cannot fix the errors. Here is my query:

SELECT klantnr,count(reisnr) as aantal
FROM (SELECT reisnr,klantnr,objectnaam
        FROM Hemelobjecten H INNER JOIN bezoeken b
        USING(objectnaam)
        INNER JOIN deelnames D
        USING(reisnr)
WHERE H.objectnaam = 'Moon') as Query
    WHERE Query.objectnaam = 'Moon' 
    GROUP BY klantnr
    HAVING count(reisnr) = MAX(Query.count(reisnr))

This gives me the error: ERROR: schema "query" does not exist

*** Error ***

ERROR: schema "query" does not exist SQL state: 3F000

How can I fix it?

The query:

SELECT klantnr,count(klantnr)
        FROM Hemelobjecten H INNER JOIN bezoeken b
        USING(objectnaam)
        INNER JOIN deelnames D
        USING(reisnr)
        WHERE H.objectnaam = 'Moon'
GROUP BY klantnr

Gives me:

125;1
122;1
124;3
121;4
123;3
126;1

I only need the row with the highest values in the right column. Thats the reason why I use

Upvotes: 2

Views: 7737

Answers (2)

wildplasser
wildplasser

Reputation: 44250

IMHO you don't need the reisnr to count the number of trips to the moon. (every record found constitutes a different trip). The code below is a (rather bulky, but very readable) CTE to count the aggregate score of trips to the moon per person and compare them in the final query, avoiding the HAVING clause:

WITH dinges AS (
        SELECT distinct klantnr
                , count(*) as aantal
        FROM Hemelobjecten H 
        INNER JOIN bezoeken b USING(objectnaam)
        INNER JOIN deelnames D USING(reisnr)
        WHERE H.objectnaam = 'Moon'
        GROUP BY klantnr
        )
SELECT klantnr
FROM dinges a
WHERE NOT EXISTS (
        SELECT *
        FROM dinges nx
        WHERE nx.aantal > a.aantal
        );

Upvotes: 0

Dan
Dan

Reputation: 4502

The HAVING clause is trying to do too much. You could write a subquery to determine the correct MAX value, but it might be considerably easier to do this:

ORDER BY count(reisnr) DESC LIMIT 1

Upvotes: 1

Related Questions