Postgresql query: schema does not exist

Question

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:

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

MAX(Query.count(reisnr)) is nonsense, the parser thinks that Query is a schema name. — wildplasser
– wildplasser, Commented Oct 23, 2013 at 12:34

Dan · Accepted Answer · 2013-10-23 12:39:40Z

1

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

answered Oct 23, 2013 at 12:39

Dan

4,55220 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wildplasser · Accepted Answer · 2013-10-23 12:48:48Z

0

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
        );

answered Oct 23, 2013 at 12:48

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

user2810895 Over a year ago

Can I solve it without "with", the teacher told me I can solve it only with a HAVING. I tryed a lot but now it gives me only those were aantal = 1 Here is the code I used: SELECT klantnr as K1, count(Q1.aantal) as K2 FROM (SELECT D.klantnr, count(D.reisnr) as aantal FROM deelnames D INNER JOIN reizen r USING(reisnr) INNER JOIN bezoeken b USING (reisnr) WHERE B.objectnaam = 'Maan' GROUP BY D.klantnr) as Q1 GROUP BY klantnr HAVING count(aantal) = (SELECT MAX(Q1.aantal));

wildplasser Over a year ago

Since the Q1 refers to a subquery, I think that would work, but maybe you dont need the SELECT on the RHS: ... HAVING count(aantal) = MAX(Q1.aantal). Why don't you try? (I would try if I had the table definitions, but I am too lazy to invent them for you) BTW: a WINDOW function could also be possible.

Collectives™ on Stack Overflow

Postgresql query: schema does not exist

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related