CODEWITHSUNDEEP
sqlpostgresql
chendriksen
chendriksen

Reputation: 1024

ERROR: Schema "x" does not exist

I'm attempting to select the users from a 'users' table that have the most occurrences of their 'order_id' property in the 'shipments' table. This is what I've tried:

SELECT users.first_name, users.email, shipments.count(order_id) 
    FROM users, shipments
    WHERE shipments.order_id = users.current_order_id
    GROUP by shipments.order_id
    ORDER by shipments.count(order_id) DESC
    LIMIT 25

But I'm getting an error of:

ERROR:  schema "shipments" does not exist

Any ideas?

Upvotes: 2

Views: 4804

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21667

You are doing the COUNT the wrong way. The tableName and column name go inside the COUNT(). or you could do COUNT(*) in this case:

SELECT users.first_name, users.email, count(shipments.order_id) 
FROM users, shipments
WHERE shipments.order_id = users.current_order_id
GROUP by users.first_name, users.email
ORDER by count(shipments.order_id) DESC
LIMIT 25

Also, you should be doing GROUP BY the columns that you select that are not the aggregate function.

Upvotes: 2

Related Questions