BEGIN; BEGIN select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%forest%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264176769.930853 ALGERIA | 1997 | 462052765.407746 ALGERIA | 1996 | 454967742.569116 ALGERIA | 1995 | 453875649.654827 ALGERIA | 1994 | 457588965.087475 ALGERIA | 1993 | 460753297.502407 ALGERIA | 1992 | 458212797.813975 ARGENTINA | 1998 | 274828475.803314 ARGENTINA | 1997 | 465220403.150839 ARGENTINA | 1996 | 471080436.589775 ARGENTINA | 1995 | 477480287.615372 ARGENTINA | 1994 | 466717817.769557 ARGENTINA | 1993 | 467779977.906004 ARGENTINA | 1992 | 472683821.822689 BRAZIL | 1998 | 270653714.148871 BRAZIL | 1997 | 471078376.095836 BRAZIL | 1996 | 469272082.002864 BRAZIL | 1995 | 478950756.205913 BRAZIL | 1994 | 470039734.815954 BRAZIL | 1993 | 467536062.818414 BRAZIL | 1992 | 465407479.36087 CANADA | 1998 | 269210118.068051 CANADA | 1997 | 470800876.25679 CANADA | 1996 | 473877934.838375 CANADA | 1995 | 469639697.105829 CANADA | 1994 | 475902691.248284 CANADA | 1993 | 463870362.54138 CANADA | 1992 | 473945846.295104 CHINA | 1998 | 275293002.452951 CHINA | 1997 | 476302361.750838 CHINA | 1996 | 471054445.33624 CHINA | 1995 | 465246676.492586 CHINA | 1994 | 476252850.112897 CHINA | 1993 | 479957684.072018 CHINA | 1992 | 474276267.560717 EGYPT | 1998 | 276157339.566021 EGYPT | 1997 | 460807198.644782 EGYPT | 1996 | 463567275.394874 EGYPT | 1995 | 462578142.609452 EGYPT | 1994 | 464614141.026469 EGYPT | 1993 | 460958427.954657 EGYPT | 1992 | 463674724.263326 ETHIOPIA | 1998 | 267677624.570428 ETHIOPIA | 1997 | 464163290.625692 ETHIOPIA | 1996 | 455026792.332122 ETHIOPIA | 1995 | 460855558.877066 ETHIOPIA | 1994 | 460977827.277705 ETHIOPIA | 1993 | 457154271.078416 ETHIOPIA | 1992 | 464204967.390262 FRANCE | 1998 | 273130943.400371 FRANCE | 1997 | 463665435.748454 FRANCE | 1996 | 462718770.056265 FRANCE | 1995 | 464213084.775049 FRANCE | 1994 | 462438500.258912 FRANCE | 1993 | 453976156.317395 FRANCE | 1992 | 463510480.362861 GERMANY | 1998 | 278735620.577105 GERMANY | 1997 | 485342880.052694 GERMANY | 1996 | 476996828.568224 GERMANY | 1995 | 473658523.677858 GERMANY | 1994 | 468648491.886929 GERMANY | 1993 | 470759435.812573 GERMANY | 1992 | 475166009.23054 INDIA | 1998 | 274427275.319329 INDIA | 1997 | 469860852.38363 INDIA | 1996 | 481034231.070167 INDIA | 1995 | 481366638.896241 INDIA | 1994 | 475629037.142125 INDIA | 1993 | 477311984.308755 INDIA | 1992 | 480731990.941815 INDONESIA | 1998 | 278751684.247779 INDONESIA | 1997 | 470726391.77403 INDONESIA | 1996 | 477849902.794284 INDONESIA | 1995 | 471831711.948042 INDONESIA | 1994 | 477387963.644019 INDONESIA | 1993 | 471514405.996006 INDONESIA | 1992 | 473069010.484564 IRAN | 1998 | 271765465.839541 IRAN | 1997 | 472254186.261705 IRAN | 1996 | 463350792.38051 IRAN | 1995 | 464684477.998034 IRAN | 1994 | 471530485.480975 IRAN | 1993 | 467837008.072126 IRAN | 1992 | 473010063.746007 IRAQ | 1998 | 272203935.204609 IRAQ | 1997 | 460689190.781773 IRAQ | 1996 | 469756311.212023 IRAQ | 1995 | 459006037.577538 IRAQ | 1994 | 464996070.21999 IRAQ | 1993 | 464556320.272837 IRAQ | 1992 | 464201452.193819 JAPAN | 1998 | 274125084.390306 JAPAN | 1997 | 469902998.380523 JAPAN | 1996 | 459507933.113126 JAPAN | 1995 | 459276648.119208 JAPAN | 1994 | 468601530.077107 JAPAN | 1993 | 467654333.964308 JAPAN | 1992 | 464742340.186787 JORDAN | 1998 | 268900564.467942 JORDAN | 1997 | 454192511.759802 JORDAN | 1996 | 463483121.890077 JORDAN | 1995 | 463472293.284079 JORDAN | 1994 | 466857856.678832 JORDAN | 1993 | 463240466.247182 JORDAN | 1992 | 463546514.11173 KENYA | 1998 | 272091694.334265 KENYA | 1997 | 476732846.679276 KENYA | 1996 | 471784209.771869 KENYA | 1995 | 477369004.692546 KENYA | 1994 | 471443220.697771 KENYA | 1993 | 476787737.161391 KENYA | 1992 | 473057173.349564 MOROCCO | 1998 | 271723572.945078 MOROCCO | 1997 | 466361458.986006 MOROCCO | 1996 | 454790546.931521 MOROCCO | 1995 | 466530841.083869 MOROCCO | 1994 | 463600283.827059 MOROCCO | 1993 | 470403207.710233 MOROCCO | 1992 | 469754680.748891 MOZAMBIQUE | 1998 | 260885066.727927 MOZAMBIQUE | 1997 | 448227454.132939 MOZAMBIQUE | 1996 | 455856954.315189 MOZAMBIQUE | 1995 | 459504769.295839 MOZAMBIQUE | 1994 | 456798450.039734 MOZAMBIQUE | 1993 | 454347126.425224 MOZAMBIQUE | 1992 | 461265484.792985 PERU | 1998 | 273854754.458912 PERU | 1997 | 465053305.981948 PERU | 1996 | 471017027.162588 PERU | 1995 | 466297118.911912 PERU | 1994 | 463083837.180165 PERU | 1993 | 464943610.970805 PERU | 1992 | 463248115.704508 ROMANIA | 1998 | 274179979.27142 ROMANIA | 1997 | 466030407.771486 ROMANIA | 1996 | 467438529.818715 ROMANIA | 1995 | 469072150.854423 ROMANIA | 1994 | 475413315.55186 ROMANIA | 1993 | 464753833.55728 ROMANIA | 1992 | 474009277.214386 RUSSIA | 1998 | 275825689.729378 RUSSIA | 1997 | 464400802.015793 RUSSIA | 1996 | 471618511.020984 RUSSIA | 1995 | 463067534.122024 RUSSIA | 1994 | 463756043.583215 RUSSIA | 1993 | 467428991.491221 RUSSIA | 1992 | 460109038.447401 SAUDI ARABIA | 1998 | 270742402.937263 SAUDI ARABIA | 1997 | 466424968.676067 SAUDI ARABIA | 1996 | 473897231.167172 SAUDI ARABIA | 1995 | 471380048.521944 SAUDI ARABIA | 1994 | 470878392.800024 SAUDI ARABIA | 1993 | 462810940.403386 SAUDI ARABIA | 1992 | 464778202.096096 UNITED KINGDOM | 1998 | 266478765.191696 UNITED KINGDOM | 1997 | 462681971.471211 UNITED KINGDOM | 1996 | 460074070.698341 UNITED KINGDOM | 1995 | 458249480.980247 UNITED KINGDOM | 1994 | 460444414.455965 UNITED KINGDOM | 1993 | 457085944.209772 UNITED KINGDOM | 1992 | 461103816.305264 UNITED STATES | 1998 | 277814770.524289 UNITED STATES | 1997 | 475347782.791485 UNITED STATES | 1996 | 470230068.829635 UNITED STATES | 1995 | 467927161.145169 UNITED STATES | 1994 | 477818350.585262 UNITED STATES | 1993 | 465402129.591236 UNITED STATES | 1992 | 473567894.507152 VIETNAM | 1998 | 272126887.188352 VIETNAM | 1997 | 458366483.273741 VIETNAM | 1996 | 462065704.398063 VIETNAM | 1995 | 454487001.631118 VIETNAM | 1994 | 454766730.210364 VIETNAM | 1993 | 459623044.930915 VIETNAM | 1992 | 456133976.188162 (175 rows) COMMIT; COMMIT