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 '%chocolate%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 273175976.35007 ALGERIA | 1997 | 471742028.499099 ALGERIA | 1996 | 468744685.080489 ALGERIA | 1995 | 456076666.220023 ALGERIA | 1994 | 462548243.86617 ALGERIA | 1993 | 458513112.40275 ALGERIA | 1992 | 460485654.081275 ARGENTINA | 1998 | 274607066.40723 ARGENTINA | 1997 | 470618413.499005 ARGENTINA | 1996 | 472565312.095797 ARGENTINA | 1995 | 474055776.600521 ARGENTINA | 1994 | 473825802.892763 ARGENTINA | 1993 | 471859466.560876 ARGENTINA | 1992 | 469771133.344295 BRAZIL | 1998 | 279810568.417436 BRAZIL | 1997 | 474825622.073346 BRAZIL | 1996 | 469530695.902071 BRAZIL | 1995 | 477101084.213987 BRAZIL | 1994 | 476934538.283544 BRAZIL | 1993 | 469846160.649638 BRAZIL | 1992 | 467149105.108895 CANADA | 1998 | 273466264.858037 CANADA | 1997 | 468474350.242261 CANADA | 1996 | 473504805.344653 CANADA | 1995 | 473327159.616153 CANADA | 1994 | 478404810.104001 CANADA | 1993 | 476179789.745274 CANADA | 1992 | 479426484.174612 CHINA | 1998 | 277072990.1648 CHINA | 1997 | 467850165.637456 CHINA | 1996 | 474740376.592934 CHINA | 1995 | 475172813.384951 CHINA | 1994 | 469449837.544747 CHINA | 1993 | 468586433.69765 CHINA | 1992 | 477852723.807506 EGYPT | 1998 | 274686039.278239 EGYPT | 1997 | 463549911.921764 EGYPT | 1996 | 457441998.512659 EGYPT | 1995 | 467093737.550249 EGYPT | 1994 | 467567376.880962 EGYPT | 1993 | 467245031.15979 EGYPT | 1992 | 465419552.994326 ETHIOPIA | 1998 | 272863998.873514 ETHIOPIA | 1997 | 455296819.783979 ETHIOPIA | 1996 | 460427723.576882 ETHIOPIA | 1995 | 464297866.868771 ETHIOPIA | 1994 | 466299683.139337 ETHIOPIA | 1993 | 461648238.475061 ETHIOPIA | 1992 | 462213754.812188 FRANCE | 1998 | 269097528.03695 FRANCE | 1997 | 458552184.878606 FRANCE | 1996 | 460620023.793251 FRANCE | 1995 | 455568948.651731 FRANCE | 1994 | 460801274.27217 FRANCE | 1993 | 458574296.433613 FRANCE | 1992 | 459322476.419559 GERMANY | 1998 | 281131198.905704 GERMANY | 1997 | 479283501.822006 GERMANY | 1996 | 473741663.808154 GERMANY | 1995 | 489351962.518693 GERMANY | 1994 | 482465338.981991 GERMANY | 1993 | 478788079.275531 GERMANY | 1992 | 483371028.844007 INDIA | 1998 | 277263698.135185 INDIA | 1997 | 475445828.318862 INDIA | 1996 | 479113962.650056 INDIA | 1995 | 479414161.435181 INDIA | 1994 | 475560634.989293 INDIA | 1993 | 479667307.091504 INDIA | 1992 | 482427010.495259 INDONESIA | 1998 | 274164728.091333 INDONESIA | 1997 | 457453211.253967 INDONESIA | 1996 | 467610043.675518 INDONESIA | 1995 | 468874178.147322 INDONESIA | 1994 | 460241574.187507 INDONESIA | 1993 | 474281259.28055 INDONESIA | 1992 | 472459499.06513 IRAN | 1998 | 275837872.003245 IRAN | 1997 | 472433447.717567 IRAN | 1996 | 467123328.935526 IRAN | 1995 | 464946819.298454 IRAN | 1994 | 470741772.182967 IRAN | 1993 | 469356512.768027 IRAN | 1992 | 469043249.876851 IRAQ | 1998 | 276112167.111495 IRAQ | 1997 | 484069274.114524 IRAQ | 1996 | 477908914.304543 IRAQ | 1995 | 473567561.352833 IRAQ | 1994 | 473621301.404878 IRAQ | 1993 | 478257892.361114 IRAQ | 1992 | 473779397.090477 JAPAN | 1998 | 271219358.928834 JAPAN | 1997 | 471752674.637689 JAPAN | 1996 | 470651179.487848 JAPAN | 1995 | 464561154.895054 JAPAN | 1994 | 458957403.459804 JAPAN | 1993 | 469310134.21891 JAPAN | 1992 | 462970814.075984 JORDAN | 1998 | 274255966.968668 JORDAN | 1997 | 466920167.093137 JORDAN | 1996 | 458478144.846394 JORDAN | 1995 | 463283670.636916 JORDAN | 1994 | 468266480.699204 JORDAN | 1993 | 472880353.078132 JORDAN | 1992 | 462338789.71627 KENYA | 1998 | 278693698.901653 KENYA | 1997 | 474881052.551144 KENYA | 1996 | 477976187.710824 KENYA | 1995 | 479893307.346304 KENYA | 1994 | 468025442.547245 KENYA | 1993 | 466731077.258972 KENYA | 1992 | 478615186.555667 MOROCCO | 1998 | 282150835.369659 MOROCCO | 1997 | 469289857.203199 MOROCCO | 1996 | 469563105.512264 MOROCCO | 1995 | 475292358.264268 MOROCCO | 1994 | 475532757.482146 MOROCCO | 1993 | 467045446.633636 MOROCCO | 1992 | 467488952.56599 MOZAMBIQUE | 1998 | 271435420.110378 MOZAMBIQUE | 1997 | 466045262.522683 MOZAMBIQUE | 1996 | 463085703.234939 MOZAMBIQUE | 1995 | 460379387.84234 MOZAMBIQUE | 1994 | 464990268.160324 MOZAMBIQUE | 1993 | 462653942.329465 MOZAMBIQUE | 1992 | 460854348.914514 PERU | 1998 | 273829685.207809 PERU | 1997 | 467309691.851851 PERU | 1996 | 468950074.66767 PERU | 1995 | 466770305.471793 PERU | 1994 | 469148027.194879 PERU | 1993 | 470783558.649718 PERU | 1992 | 470608845.18594 ROMANIA | 1998 | 277484232.272825 ROMANIA | 1997 | 476918882.492471 ROMANIA | 1996 | 482656637.207566 ROMANIA | 1995 | 465823885.160297 ROMANIA | 1994 | 465599731.639768 ROMANIA | 1993 | 466816377.457183 ROMANIA | 1992 | 470952076.354436 RUSSIA | 1998 | 280648640.169917 RUSSIA | 1997 | 474152476.411057 RUSSIA | 1996 | 473641586.144497 RUSSIA | 1995 | 478134727.471425 RUSSIA | 1994 | 470790624.596327 RUSSIA | 1993 | 480354457.043196 RUSSIA | 1992 | 473439892.100618 SAUDI ARABIA | 1998 | 279433378.84915 SAUDI ARABIA | 1997 | 471467254.733452 SAUDI ARABIA | 1996 | 472156702.69909 SAUDI ARABIA | 1995 | 468376576.675687 SAUDI ARABIA | 1994 | 467701907.649461 SAUDI ARABIA | 1993 | 469058064.153018 SAUDI ARABIA | 1992 | 468494556.10992 UNITED KINGDOM | 1998 | 265189958.25338 UNITED KINGDOM | 1997 | 457168402.480034 UNITED KINGDOM | 1996 | 466684047.556083 UNITED KINGDOM | 1995 | 460022572.497736 UNITED KINGDOM | 1994 | 462281924.348381 UNITED KINGDOM | 1993 | 459845127.523806 UNITED KINGDOM | 1992 | 459706641.568402 UNITED STATES | 1998 | 285186748.572768 UNITED STATES | 1997 | 470607913.158711 UNITED STATES | 1996 | 477349914.976395 UNITED STATES | 1995 | 474584811.878991 UNITED STATES | 1994 | 480133986.92321 UNITED STATES | 1993 | 472951441.683805 UNITED STATES | 1992 | 477599433.66367 VIETNAM | 1998 | 271946510.720343 VIETNAM | 1997 | 465802137.410854 VIETNAM | 1996 | 478636745.535965 VIETNAM | 1995 | 464637840.923614 VIETNAM | 1994 | 470556891.847416 VIETNAM | 1993 | 475403721.373037 VIETNAM | 1992 | 469596683.931018 (175 rows) COMMIT; COMMIT