I have three tables like this:

Objects (NumId is Primary key)

Collection (ColName is Primary Key)

Borrowed

(NumObj is Foreign Key/Primary Key referring Object table, and Collection_name is Foreign Key referring Collection table)

The query I need to do is: "Which collections (excluding the permanent) have the highest total cost of borrowed objects?"

my current attempt (that doesn't work):

SELECT ColName FROM Colecao
WHERE (
   SELECT MAX((SUM(Price)) FROM Objects AS Num
   JOIN Borrowed ON NumObj = Objects.NumId
   JOIN Collection ON ColName = Collection_name
   WHERE Num > COUNT(NumId) FROM Objects 
   WHERE Object_category = "permanent"
);

It returns message: "syntax error"

CodePudding user response：

"Which collections [...] have the highest total cost of borrowed objects?"

select
    b.Collection_name,
    sum(o.price) Worth
from
    Objects o
    inner join Borrowed b on b.NumObj = o.NumId
where
    o.Object_category = 'borrowed'
group by
    b.Collection_name
order by
    sum(o.price) desc

gives you a list of all collections ordered descending by the overall worth of the borrowed objects. You could add limit 1 to show the top item of the list.

But imagine there are two collections that happen to have the same total worth. They would both have "the highest total cost of borrowed objects", so both should be returned. The above query with limit 1 would omit one of them.

In this case we could rank the collections according to their worth, and return all rows with rank = 1.

select
    *
from
    (
    select
        *,
        rank() over (order by Worth desc) rnk
    from
        (
        select
            b.Collection_name,
            sum(o.price) Worth
        from
            Objects o
            inner join Borrowed b on b.NumObj = o.NumId
        where
            o.Object_category = 'borrowed'
        group by
            b.Collection_name
        ) collections
    ) collections_ranked
where
    rnk = 1
order by
    Collection_name

Alternatively, we could set up a CTE and express the same thing like this:

with
    collections as (
        select
            b.Collection_name,
            sum(o.price) Worth
        from
            Objects o
            inner join Borrowed b on b.NumObj = o.NumId
        where
            o.Object_category = 'borrowed'
        group by
            b.Collection_name
    )
select
    Collection_name,
    Worth
from
    collections
where
    Worth = (select max(worth) from collections)
order by
    Collection_name