How do i select a max of a sum with multiple tables-CodePudding

I need some help with a problem. I am trying to get a max from a sum with three tables. I've been trying to figure it out with help from other post on here but so far none of them have helped me. I manage to get the right answer when using limit 1 but i was wondering how I can do it without that.

The three tables are

Tables

So far I got this

(select vnamn, sum(mangd) as a from land, export, varldsdel where varldsdel.vkod=land.vkod and land.landkod=export.landkod and ar=2004 group by vnamn)

this give me the following result

 ---------------- ------------- 
|     vnamn      | sum(mangd)  |
 ---------------- ------------- 
| sydamerika     |       61000 |
| noramerika     |       50000 |
| europa         |       1200  |
 ---------------- -------------

Now this is where I get stuck. I want to do a max of this result so that only "sydamerika | 61000" shows and I've been trying to do this all day but can't get the hang of it.

Thanks

CodePudding user response：

since you already have the select query, you can do this instead;

Select vnamn, max(a) as mangd from (select vnamn, sum(mangd) as a from land, export, varldsdel where varldsdel.vkod=land.vkod and land.landkod=export.landkod and ar=2004 group by vnamn) k

CodePudding user response：

Does the following query work in your environment?

SELECT 
    vnamn, 
    MAX(a) AS mangd 
FROM (
    SELECT 
        vnamn, 
        SUM(mangd) AS a 
    FROM 
        land
    INNER JOIN 
        export
    ON
        land.landkod=export.landkod
    INNER JOIN 
        varldsdel 
    ON 
        varldsdel.vkod=land.vkod 
    WHERE 
        ar = 2004 
    GROUP BY 
        vnamn
    ) k
GROUP BY 
    vnamn