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 '%orchid%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269095125.346231 ALGERIA | 1997 | 455844764.368213 ALGERIA | 1996 | 461073455.51367 ALGERIA | 1995 | 471207013.350864 ALGERIA | 1994 | 462615512.426977 ALGERIA | 1993 | 460700322.548371 ALGERIA | 1992 | 464770807.145931 ARGENTINA | 1998 | 272511655.436108 ARGENTINA | 1997 | 471672871.351516 ARGENTINA | 1996 | 466135363.931637 ARGENTINA | 1995 | 462614757.784221 ARGENTINA | 1994 | 470254596.338732 ARGENTINA | 1993 | 464302978.724435 ARGENTINA | 1992 | 458499360.537392 BRAZIL | 1998 | 266293078.969202 BRAZIL | 1997 | 470890294.473845 BRAZIL | 1996 | 469136902.601038 BRAZIL | 1995 | 470805697.676428 BRAZIL | 1994 | 466237306.24639 BRAZIL | 1993 | 465574802.90254 BRAZIL | 1992 | 468287714.8394 CANADA | 1998 | 280986008.55721 CANADA | 1997 | 475033624.397207 CANADA | 1996 | 475387852.327302 CANADA | 1995 | 477297640.325063 CANADA | 1994 | 474461067.496754 CANADA | 1993 | 475749395.838771 CANADA | 1992 | 474998345.212274 CHINA | 1998 | 274163731.582884 CHINA | 1997 | 472327880.743305 CHINA | 1996 | 468137809.860993 CHINA | 1995 | 464996970.415739 CHINA | 1994 | 467619649.793043 CHINA | 1993 | 467221006.632492 CHINA | 1992 | 468060303.203825 EGYPT | 1998 | 273606706.896427 EGYPT | 1997 | 459656385.540613 EGYPT | 1996 | 472872554.375336 EGYPT | 1995 | 460225218.037166 EGYPT | 1994 | 462898590.797567 EGYPT | 1993 | 468104993.283063 EGYPT | 1992 | 462738311.992103 ETHIOPIA | 1998 | 265773347.238545 ETHIOPIA | 1997 | 465054557.770144 ETHIOPIA | 1996 | 457567387.980389 ETHIOPIA | 1995 | 460061463.794311 ETHIOPIA | 1994 | 462311299.903006 ETHIOPIA | 1993 | 460623750.672622 ETHIOPIA | 1992 | 466226621.101644 FRANCE | 1998 | 269307761.442043 FRANCE | 1997 | 466368998.410935 FRANCE | 1996 | 461021977.07109 FRANCE | 1995 | 458552108.035305 FRANCE | 1994 | 458444095.515389 FRANCE | 1993 | 458331922.197934 FRANCE | 1992 | 457290489.029174 GERMANY | 1998 | 271998091.424882 GERMANY | 1997 | 475540651.178147 GERMANY | 1996 | 471798703.720852 GERMANY | 1995 | 475250183.487658 GERMANY | 1994 | 470635832.159822 GERMANY | 1993 | 477883280.726344 GERMANY | 1992 | 470139144.810285 INDIA | 1998 | 275334181.896824 INDIA | 1997 | 476341303.719211 INDIA | 1996 | 482590253.329953 INDIA | 1995 | 476583456.245242 INDIA | 1994 | 477561791.992552 INDIA | 1993 | 482760898.068488 INDIA | 1992 | 476678672.875798 INDONESIA | 1998 | 275892101.142276 INDONESIA | 1997 | 464221264.798582 INDONESIA | 1996 | 461281714.028512 INDONESIA | 1995 | 449859754.181339 INDONESIA | 1994 | 459150552.060217 INDONESIA | 1993 | 471315588.880018 INDONESIA | 1992 | 461096753.247461 IRAN | 1998 | 268052050.240116 IRAN | 1997 | 467869457.131869 IRAN | 1996 | 468146043.018204 IRAN | 1995 | 471992766.256335 IRAN | 1994 | 463747005.28373 IRAN | 1993 | 470932361.029654 IRAN | 1992 | 476506530.467159 IRAQ | 1998 | 280174415.407172 IRAQ | 1997 | 471516992.052251 IRAQ | 1996 | 467744946.130743 IRAQ | 1995 | 471826018.543744 IRAQ | 1994 | 474282986.726905 IRAQ | 1993 | 475945862.586414 IRAQ | 1992 | 476930197.479704 JAPAN | 1998 | 269576656.522484 JAPAN | 1997 | 464152665.145119 JAPAN | 1996 | 457817006.003714 JAPAN | 1995 | 463178961.836917 JAPAN | 1994 | 458231827.855152 JAPAN | 1993 | 464904869.870735 JAPAN | 1992 | 472494889.759559 JORDAN | 1998 | 267149906.757046 JORDAN | 1997 | 465311804.21549 JORDAN | 1996 | 459714296.052595 JORDAN | 1995 | 466809040.477404 JORDAN | 1994 | 464966134.72875 JORDAN | 1993 | 464596066.755567 JORDAN | 1992 | 460805453.067662 KENYA | 1998 | 283111480.804649 KENYA | 1997 | 473633087.323888 KENYA | 1996 | 480674980.669281 KENYA | 1995 | 476726547.429926 KENYA | 1994 | 477351380.20219 KENYA | 1993 | 484001690.930261 KENYA | 1992 | 476196140.659209 MOROCCO | 1998 | 277119060.420038 MOROCCO | 1997 | 474619794.433615 MOROCCO | 1996 | 481129731.158993 MOROCCO | 1995 | 470795112.005217 MOROCCO | 1994 | 478555844.619418 MOROCCO | 1993 | 479819382.024073 MOROCCO | 1992 | 475984966.213515 MOZAMBIQUE | 1998 | 268389356.20474 MOZAMBIQUE | 1997 | 467885046.252369 MOZAMBIQUE | 1996 | 470150122.947188 MOZAMBIQUE | 1995 | 464470156.936949 MOZAMBIQUE | 1994 | 458558082.933543 MOZAMBIQUE | 1993 | 467007149.469908 MOZAMBIQUE | 1992 | 458816302.327963 PERU | 1998 | 276025442.672099 PERU | 1997 | 466021150.800256 PERU | 1996 | 474513292.926342 PERU | 1995 | 464192600.75809 PERU | 1994 | 474789367.510849 PERU | 1993 | 477172500.225189 PERU | 1992 | 475869857.991743 ROMANIA | 1998 | 271468716.623132 ROMANIA | 1997 | 470281000.634679 ROMANIA | 1996 | 475209786.221756 ROMANIA | 1995 | 472230952.922499 ROMANIA | 1994 | 466473070.533137 ROMANIA | 1993 | 462098885.898871 ROMANIA | 1992 | 470541819.805655 RUSSIA | 1998 | 276415736.002823 RUSSIA | 1997 | 474685379.574721 RUSSIA | 1996 | 475306611.374356 RUSSIA | 1995 | 467599728.990969 RUSSIA | 1994 | 476061644.174434 RUSSIA | 1993 | 468857504.512114 RUSSIA | 1992 | 468253581.057288 SAUDI ARABIA | 1998 | 273942038.249563 SAUDI ARABIA | 1997 | 462684668.341293 SAUDI ARABIA | 1996 | 468325656.337567 SAUDI ARABIA | 1995 | 469406542.72206 SAUDI ARABIA | 1994 | 471974771.569707 SAUDI ARABIA | 1993 | 473993624.682035 SAUDI ARABIA | 1992 | 466811372.856557 UNITED KINGDOM | 1998 | 269098600.427254 UNITED KINGDOM | 1997 | 454161764.083765 UNITED KINGDOM | 1996 | 461020824.865191 UNITED KINGDOM | 1995 | 459288373.138924 UNITED KINGDOM | 1994 | 462667993.185278 UNITED KINGDOM | 1993 | 462821732.251176 UNITED KINGDOM | 1992 | 466885351.576651 UNITED STATES | 1998 | 280743482.585581 UNITED STATES | 1997 | 479095819.291614 UNITED STATES | 1996 | 473380899.320628 UNITED STATES | 1995 | 476848960.76483 UNITED STATES | 1994 | 475698887.122396 UNITED STATES | 1993 | 474713336.11689 UNITED STATES | 1992 | 485548269.37852 VIETNAM | 1998 | 271056615.190842 VIETNAM | 1997 | 465623185.683923 VIETNAM | 1996 | 470604034.564055 VIETNAM | 1995 | 462278052.171153 VIETNAM | 1994 | 468554600.000404 VIETNAM | 1993 | 466259556.727147 VIETNAM | 1992 | 461540770.564838 (175 rows) COMMIT; COMMIT