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 '%azure%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271401129.09645 ALGERIA | 1997 | 456716713.80531 ALGERIA | 1996 | 458580358.625007 ALGERIA | 1995 | 461876091.613816 ALGERIA | 1994 | 464104870.031119 ALGERIA | 1993 | 458979129.550897 ALGERIA | 1992 | 465789476.031747 ARGENTINA | 1998 | 283794271.849078 ARGENTINA | 1997 | 477482066.15228 ARGENTINA | 1996 | 482154545.409 ARGENTINA | 1995 | 471875061.266007 ARGENTINA | 1994 | 474232593.105471 ARGENTINA | 1993 | 467115566.745425 ARGENTINA | 1992 | 476540775.441926 BRAZIL | 1998 | 271920541.745872 BRAZIL | 1997 | 474076287.694786 BRAZIL | 1996 | 475642646.074556 BRAZIL | 1995 | 464848779.02953 BRAZIL | 1994 | 465354363.934492 BRAZIL | 1993 | 474301796.909455 BRAZIL | 1992 | 473994930.4551 CANADA | 1998 | 280659929.923616 CANADA | 1997 | 473196683.172585 CANADA | 1996 | 477063421.621533 CANADA | 1995 | 480883337.750606 CANADA | 1994 | 485174699.786419 CANADA | 1993 | 475117681.230652 CANADA | 1992 | 481436869.760697 CHINA | 1998 | 277305016.349091 CHINA | 1997 | 468262326.924569 CHINA | 1996 | 473337999.14583 CHINA | 1995 | 468674585.623659 CHINA | 1994 | 459820037.272266 CHINA | 1993 | 471029755.907047 CHINA | 1992 | 474517215.20694 EGYPT | 1998 | 263930815.469631 EGYPT | 1997 | 460479966.919393 EGYPT | 1996 | 460410627.111875 EGYPT | 1995 | 459076507.974769 EGYPT | 1994 | 464676894.864248 EGYPT | 1993 | 460506859.983983 EGYPT | 1992 | 454301901.583256 ETHIOPIA | 1998 | 281679352.853554 ETHIOPIA | 1997 | 459546906.307747 ETHIOPIA | 1996 | 461245758.51581 ETHIOPIA | 1995 | 467869529.792235 ETHIOPIA | 1994 | 465609975.815789 ETHIOPIA | 1993 | 470311181.842785 ETHIOPIA | 1992 | 458527320.431625 FRANCE | 1998 | 271251576.768729 FRANCE | 1997 | 467414656.567009 FRANCE | 1996 | 466540038.479212 FRANCE | 1995 | 460793673.279227 FRANCE | 1994 | 462644499.303156 FRANCE | 1993 | 462196523.615671 FRANCE | 1992 | 465828767.187714 GERMANY | 1998 | 279387555.070995 GERMANY | 1997 | 477500710.791625 GERMANY | 1996 | 475181518.385687 GERMANY | 1995 | 481441033.844476 GERMANY | 1994 | 482190553.509708 GERMANY | 1993 | 480088508.910597 GERMANY | 1992 | 471066369.819713 INDIA | 1998 | 272443708.814812 INDIA | 1997 | 467473553.414786 INDIA | 1996 | 479876539.131761 INDIA | 1995 | 472337204.306778 INDIA | 1994 | 474311282.031647 INDIA | 1993 | 468938026.03043 INDIA | 1992 | 472121717.340278 INDONESIA | 1998 | 269499299.090519 INDONESIA | 1997 | 459424551.446596 INDONESIA | 1996 | 470452351.52149 INDONESIA | 1995 | 458246744.655234 INDONESIA | 1994 | 450739178.297441 INDONESIA | 1993 | 464057604.464476 INDONESIA | 1992 | 456875623.796579 IRAN | 1998 | 273267588.406077 IRAN | 1997 | 462306234.700438 IRAN | 1996 | 464311070.941022 IRAN | 1995 | 472925284.994314 IRAN | 1994 | 466385599.320656 IRAN | 1993 | 463303323.812968 IRAN | 1992 | 457592444.188403 IRAQ | 1998 | 273999490.432716 IRAQ | 1997 | 458494293.189331 IRAQ | 1996 | 467571686.572017 IRAQ | 1995 | 465147839.388815 IRAQ | 1994 | 465000952.090465 IRAQ | 1993 | 465906125.621023 IRAQ | 1992 | 474249193.224891 JAPAN | 1998 | 274191237.326565 JAPAN | 1997 | 471137842.576499 JAPAN | 1996 | 467857592.411759 JAPAN | 1995 | 464723074.646428 JAPAN | 1994 | 471226668.42782 JAPAN | 1993 | 471680552.906106 JAPAN | 1992 | 462258333.529931 JORDAN | 1998 | 268239514.929072 JORDAN | 1997 | 465194905.548184 JORDAN | 1996 | 464509239.566808 JORDAN | 1995 | 460571622.43107 JORDAN | 1994 | 465990071.905117 JORDAN | 1993 | 461594083.478555 JORDAN | 1992 | 462295912.38879 KENYA | 1998 | 277334356.845158 KENYA | 1997 | 473410383.637565 KENYA | 1996 | 471680054.387862 KENYA | 1995 | 472448224.563536 KENYA | 1994 | 470169250.054753 KENYA | 1993 | 471267320.929093 KENYA | 1992 | 473006552.925869 MOROCCO | 1998 | 276108285.527223 MOROCCO | 1997 | 473207903.159601 MOROCCO | 1996 | 472048362.543497 MOROCCO | 1995 | 477653571.795702 MOROCCO | 1994 | 466035383.549429 MOROCCO | 1993 | 472597637.530118 MOROCCO | 1992 | 476677760.295896 MOZAMBIQUE | 1998 | 276837553.736809 MOZAMBIQUE | 1997 | 463784193.323713 MOZAMBIQUE | 1996 | 463703083.492168 MOZAMBIQUE | 1995 | 463462591.584673 MOZAMBIQUE | 1994 | 463628272.840976 MOZAMBIQUE | 1993 | 470773009.175707 MOZAMBIQUE | 1992 | 464449069.95923 PERU | 1998 | 274734933.863147 PERU | 1997 | 465652347.962261 PERU | 1996 | 469589500.014651 PERU | 1995 | 455926023.354903 PERU | 1994 | 466967318.935591 PERU | 1993 | 459584015.048656 PERU | 1992 | 462426708.425571 ROMANIA | 1998 | 273184669.112602 ROMANIA | 1997 | 467274325.730533 ROMANIA | 1996 | 470581777.498213 ROMANIA | 1995 | 468856735.711146 ROMANIA | 1994 | 456066896.696351 ROMANIA | 1993 | 457222631.742138 ROMANIA | 1992 | 473509953.045018 RUSSIA | 1998 | 271987554.384339 RUSSIA | 1997 | 468219912.488717 RUSSIA | 1996 | 459702694.72584 RUSSIA | 1995 | 467787936.20585 RUSSIA | 1994 | 468047959.77671 RUSSIA | 1993 | 466627273.614322 RUSSIA | 1992 | 469197146.098769 SAUDI ARABIA | 1998 | 274833267.940295 SAUDI ARABIA | 1997 | 475281613.343405 SAUDI ARABIA | 1996 | 472369944.855486 SAUDI ARABIA | 1995 | 468396629.273514 SAUDI ARABIA | 1994 | 466571122.304301 SAUDI ARABIA | 1993 | 476036708.135196 SAUDI ARABIA | 1992 | 477050017.914339 UNITED KINGDOM | 1998 | 273925418.113193 UNITED KINGDOM | 1997 | 465332625.370344 UNITED KINGDOM | 1996 | 461344530.376618 UNITED KINGDOM | 1995 | 460860202.183691 UNITED KINGDOM | 1994 | 461049896.886352 UNITED KINGDOM | 1993 | 466824414.727889 UNITED KINGDOM | 1992 | 461047561.928011 UNITED STATES | 1998 | 271241142.682496 UNITED STATES | 1997 | 474988802.961967 UNITED STATES | 1996 | 481730649.00175 UNITED STATES | 1995 | 478004406.836134 UNITED STATES | 1994 | 475659022.683909 UNITED STATES | 1993 | 467982545.374963 UNITED STATES | 1992 | 473990471.646017 VIETNAM | 1998 | 271862500.314444 VIETNAM | 1997 | 458405117.581719 VIETNAM | 1996 | 473956046.118255 VIETNAM | 1995 | 459111050.617675 VIETNAM | 1994 | 467074078.53425 VIETNAM | 1993 | 463052507.85301 VIETNAM | 1992 | 462810316.859277 (175 rows) COMMIT; COMMIT