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 '%thistle%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267071115.739323 ALGERIA | 1997 | 463505372.123394 ALGERIA | 1996 | 455834912.734992 ALGERIA | 1995 | 457501486.283709 ALGERIA | 1994 | 454856912.278131 ALGERIA | 1993 | 452654093.166861 ALGERIA | 1992 | 456692175.867393 ARGENTINA | 1998 | 273992371.521548 ARGENTINA | 1997 | 475592869.432119 ARGENTINA | 1996 | 467319634.635341 ARGENTINA | 1995 | 467069944.595845 ARGENTINA | 1994 | 470019285.674544 ARGENTINA | 1993 | 474778460.883616 ARGENTINA | 1992 | 468745750.007302 BRAZIL | 1998 | 269947990.577033 BRAZIL | 1997 | 462192950.810641 BRAZIL | 1996 | 459052355.058707 BRAZIL | 1995 | 458055978.517212 BRAZIL | 1994 | 457409729.707401 BRAZIL | 1993 | 460569894.473746 BRAZIL | 1992 | 466365090.024484 CANADA | 1998 | 276517937.318102 CANADA | 1997 | 477249161.042975 CANADA | 1996 | 478967840.126882 CANADA | 1995 | 472586027.556637 CANADA | 1994 | 483495070.30021 CANADA | 1993 | 480256970.713 CANADA | 1992 | 481834682.644827 CHINA | 1998 | 270692579.283731 CHINA | 1997 | 457699622.330387 CHINA | 1996 | 459628506.647626 CHINA | 1995 | 457016059.808057 CHINA | 1994 | 459656389.229337 CHINA | 1993 | 462625396.964029 CHINA | 1992 | 464572179.887134 EGYPT | 1998 | 268634318.935103 EGYPT | 1997 | 464349077.715498 EGYPT | 1996 | 458939061.647614 EGYPT | 1995 | 467895522.35644 EGYPT | 1994 | 459554744.200186 EGYPT | 1993 | 460780073.473411 EGYPT | 1992 | 463867020.00504 ETHIOPIA | 1998 | 266725514.729864 ETHIOPIA | 1997 | 458810862.895021 ETHIOPIA | 1996 | 455498075.703038 ETHIOPIA | 1995 | 458811329.941864 ETHIOPIA | 1994 | 460516337.964148 ETHIOPIA | 1993 | 453397700.717346 ETHIOPIA | 1992 | 456992502.921563 FRANCE | 1998 | 272069419.467917 FRANCE | 1997 | 457721610.065821 FRANCE | 1996 | 461186659.552704 FRANCE | 1995 | 454551817.085971 FRANCE | 1994 | 453571910.800002 FRANCE | 1993 | 459236016.96374 FRANCE | 1992 | 462987955.705397 GERMANY | 1998 | 270906864.912855 GERMANY | 1997 | 465364434.670086 GERMANY | 1996 | 464311779.503484 GERMANY | 1995 | 465587971.13107 GERMANY | 1994 | 468016595.501758 GERMANY | 1993 | 470742003.916487 GERMANY | 1992 | 466778687.174937 INDIA | 1998 | 274413523.102734 INDIA | 1997 | 475366687.66382 INDIA | 1996 | 474654417.177313 INDIA | 1995 | 477831812.606386 INDIA | 1994 | 474985901.934851 INDIA | 1993 | 475153878.909047 INDIA | 1992 | 470485380.649234 INDONESIA | 1998 | 272131774.043867 INDONESIA | 1997 | 449662128.655305 INDONESIA | 1996 | 455486270.54781 INDONESIA | 1995 | 455483287.622815 INDONESIA | 1994 | 455405903.38756 INDONESIA | 1993 | 461497074.821056 INDONESIA | 1992 | 456902618.837721 IRAN | 1998 | 264996586.163341 IRAN | 1997 | 470045048.953349 IRAN | 1996 | 461783387.923329 IRAN | 1995 | 461894470.183253 IRAN | 1994 | 459051389.486295 IRAN | 1993 | 459804580.338292 IRAN | 1992 | 464793567.069738 IRAQ | 1998 | 272229358.777291 IRAQ | 1997 | 466533696.881465 IRAQ | 1996 | 471361167.002161 IRAQ | 1995 | 464121642.307479 IRAQ | 1994 | 466993754.102762 IRAQ | 1993 | 462261333.974417 IRAQ | 1992 | 465249447.115285 JAPAN | 1998 | 270068232.247749 JAPAN | 1997 | 451389056.20872 JAPAN | 1996 | 455576923.49989 JAPAN | 1995 | 454741389.276597 JAPAN | 1994 | 452040121.027185 JAPAN | 1993 | 464300633.145029 JAPAN | 1992 | 451599636.978101 JORDAN | 1998 | 268082109.291864 JORDAN | 1997 | 457158242.763519 JORDAN | 1996 | 458997940.742486 JORDAN | 1995 | 447426988.013046 JORDAN | 1994 | 456012761.812618 JORDAN | 1993 | 453709275.489645 JORDAN | 1992 | 458038245.204006 KENYA | 1998 | 270739094.749036 KENYA | 1997 | 464891073.218073 KENYA | 1996 | 473522362.162502 KENYA | 1995 | 466915910.932352 KENYA | 1994 | 466059787.33422 KENYA | 1993 | 475073555.435649 KENYA | 1992 | 472021924.278176 MOROCCO | 1998 | 266976196.885421 MOROCCO | 1997 | 462949285.866887 MOROCCO | 1996 | 463360559.628646 MOROCCO | 1995 | 458524260.612535 MOROCCO | 1994 | 458872173.336738 MOROCCO | 1993 | 465594028.85649 MOROCCO | 1992 | 465892248.725847 MOZAMBIQUE | 1998 | 269255630.555047 MOZAMBIQUE | 1997 | 450000734.303134 MOZAMBIQUE | 1996 | 454969566.302181 MOZAMBIQUE | 1995 | 452480891.722344 MOZAMBIQUE | 1994 | 457848199.072528 MOZAMBIQUE | 1993 | 455600288.761053 MOZAMBIQUE | 1992 | 448138886.407466 PERU | 1998 | 269787641.175791 PERU | 1997 | 464762550.244281 PERU | 1996 | 467374404.878048 PERU | 1995 | 456461223.546142 PERU | 1994 | 466969173.214919 PERU | 1993 | 464991657.928966 PERU | 1992 | 460645010.348471 ROMANIA | 1998 | 268598226.931606 ROMANIA | 1997 | 458870249.008745 ROMANIA | 1996 | 461254041.028758 ROMANIA | 1995 | 456541469.147337 ROMANIA | 1994 | 464402103.247366 ROMANIA | 1993 | 460590255.206478 ROMANIA | 1992 | 465471480.122732 RUSSIA | 1998 | 280476164.991734 RUSSIA | 1997 | 467427199.773083 RUSSIA | 1996 | 467167860.65111 RUSSIA | 1995 | 472422253.323507 RUSSIA | 1994 | 470483222.50138 RUSSIA | 1993 | 476273507.087646 RUSSIA | 1992 | 466688382.402103 SAUDI ARABIA | 1998 | 270617638.711821 SAUDI ARABIA | 1997 | 457382235.770124 SAUDI ARABIA | 1996 | 465483610.529979 SAUDI ARABIA | 1995 | 467926419.840036 SAUDI ARABIA | 1994 | 463970707.874658 SAUDI ARABIA | 1993 | 463238159.271021 SAUDI ARABIA | 1992 | 467749517.381787 UNITED KINGDOM | 1998 | 266811281.207762 UNITED KINGDOM | 1997 | 460387159.994832 UNITED KINGDOM | 1996 | 458675050.090743 UNITED KINGDOM | 1995 | 462279908.398679 UNITED KINGDOM | 1994 | 462919129.653624 UNITED KINGDOM | 1993 | 467686596.153773 UNITED KINGDOM | 1992 | 457582479.153416 UNITED STATES | 1998 | 274007904.593094 UNITED STATES | 1997 | 468306388.301365 UNITED STATES | 1996 | 477663951.146378 UNITED STATES | 1995 | 465836991.041472 UNITED STATES | 1994 | 475050252.369218 UNITED STATES | 1993 | 469278475.725596 UNITED STATES | 1992 | 472575632.243596 VIETNAM | 1998 | 271624522.54411 VIETNAM | 1997 | 465437343.731099 VIETNAM | 1996 | 460903981.990906 VIETNAM | 1995 | 460178906.10059 VIETNAM | 1994 | 465338947.943809 VIETNAM | 1993 | 467524117.353888 VIETNAM | 1992 | 462162412.366718 (175 rows) COMMIT; COMMIT