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 '%dark%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265641508.75709 ALGERIA | 1997 | 443255202.292149 ALGERIA | 1996 | 452866137.903436 ALGERIA | 1995 | 447170300.702932 ALGERIA | 1994 | 453002892.315028 ALGERIA | 1993 | 447997806.259067 ALGERIA | 1992 | 453083355.853883 ARGENTINA | 1998 | 269846450.666927 ARGENTINA | 1997 | 467262530.231307 ARGENTINA | 1996 | 469229605.059231 ARGENTINA | 1995 | 461335758.2422 ARGENTINA | 1994 | 466576474.008191 ARGENTINA | 1993 | 473113666.664652 ARGENTINA | 1992 | 463183491.700433 BRAZIL | 1998 | 268977722.517327 BRAZIL | 1997 | 463852671.396483 BRAZIL | 1996 | 469477892.061786 BRAZIL | 1995 | 468230824.331361 BRAZIL | 1994 | 480070303.082535 BRAZIL | 1993 | 468154588.759254 BRAZIL | 1992 | 467436674.027315 CANADA | 1998 | 279707891.115576 CANADA | 1997 | 464839231.873186 CANADA | 1996 | 473510675.515362 CANADA | 1995 | 472156964.177295 CANADA | 1994 | 486422195.550739 CANADA | 1993 | 476904280.639642 CANADA | 1992 | 471525524.042543 CHINA | 1998 | 277975786.103948 CHINA | 1997 | 475390407.701159 CHINA | 1996 | 472592953.265836 CHINA | 1995 | 474781457.086939 CHINA | 1994 | 480505366.144097 CHINA | 1993 | 473459559.420042 CHINA | 1992 | 474768453.236316 EGYPT | 1998 | 272929290.058251 EGYPT | 1997 | 464714614.943477 EGYPT | 1996 | 469444669.890619 EGYPT | 1995 | 454848040.239947 EGYPT | 1994 | 454645898.22017 EGYPT | 1993 | 457445346.139483 EGYPT | 1992 | 461218348.895215 ETHIOPIA | 1998 | 268487865.95676 ETHIOPIA | 1997 | 462902822.594627 ETHIOPIA | 1996 | 464423723.096024 ETHIOPIA | 1995 | 461558361.286106 ETHIOPIA | 1994 | 463389478.001842 ETHIOPIA | 1993 | 460024167.786768 ETHIOPIA | 1992 | 465763888.033571 FRANCE | 1998 | 270598315.466926 FRANCE | 1997 | 455523216.06938 FRANCE | 1996 | 459309064.333492 FRANCE | 1995 | 451957936.903223 FRANCE | 1994 | 467603962.567531 FRANCE | 1993 | 455661936.576872 FRANCE | 1992 | 464674607.204051 GERMANY | 1998 | 272322393.360975 GERMANY | 1997 | 472639509.9732 GERMANY | 1996 | 476759650.800948 GERMANY | 1995 | 472789989.968616 GERMANY | 1994 | 469775681.296493 GERMANY | 1993 | 476915552.740886 GERMANY | 1992 | 475762966.724143 INDIA | 1998 | 278794526.700174 INDIA | 1997 | 470254951.567218 INDIA | 1996 | 476924780.805732 INDIA | 1995 | 472415053.545957 INDIA | 1994 | 473783639.640736 INDIA | 1993 | 477268816.118673 INDIA | 1992 | 474364234.019082 INDONESIA | 1998 | 268939302.569894 INDONESIA | 1997 | 456625733.678691 INDONESIA | 1996 | 460359445.431061 INDONESIA | 1995 | 462387027.134041 INDONESIA | 1994 | 457874613.751125 INDONESIA | 1993 | 460644705.503607 INDONESIA | 1992 | 453862246.298729 IRAN | 1998 | 270528668.522584 IRAN | 1997 | 463248130.371696 IRAN | 1996 | 460459582.434087 IRAN | 1995 | 460793884.94892 IRAN | 1994 | 450755689.801688 IRAN | 1993 | 455675263.577292 IRAN | 1992 | 458483623.092289 IRAQ | 1998 | 278139318.818272 IRAQ | 1997 | 473138893.24333 IRAQ | 1996 | 481879874.861499 IRAQ | 1995 | 478538914.966142 IRAQ | 1994 | 477449068.659584 IRAQ | 1993 | 479808831.648946 IRAQ | 1992 | 465510583.168738 JAPAN | 1998 | 273633673.035853 JAPAN | 1997 | 465161594.006361 JAPAN | 1996 | 462378991.89117 JAPAN | 1995 | 469167534.474457 JAPAN | 1994 | 467143532.35494 JAPAN | 1993 | 463291202.018634 JAPAN | 1992 | 457383191.379856 JORDAN | 1998 | 267604312.838334 JORDAN | 1997 | 452729779.845533 JORDAN | 1996 | 462761763.251864 JORDAN | 1995 | 456375289.640921 JORDAN | 1994 | 455703782.810341 JORDAN | 1993 | 462373622.913121 JORDAN | 1992 | 463282642.274372 KENYA | 1998 | 279558323.621803 KENYA | 1997 | 472155351.524683 KENYA | 1996 | 474040688.070848 KENYA | 1995 | 471344077.922638 KENYA | 1994 | 475771218.680804 KENYA | 1993 | 484966690.838595 KENYA | 1992 | 473988878.887406 MOROCCO | 1998 | 269987196.204017 MOROCCO | 1997 | 470251282.294932 MOROCCO | 1996 | 474083580.531559 MOROCCO | 1995 | 466639883.53226 MOROCCO | 1994 | 467426388.18678 MOROCCO | 1993 | 464724518.957896 MOROCCO | 1992 | 476204758.146759 MOZAMBIQUE | 1998 | 273656707.064714 MOZAMBIQUE | 1997 | 457071908.244411 MOZAMBIQUE | 1996 | 463594575.763381 MOZAMBIQUE | 1995 | 461452250.721754 MOZAMBIQUE | 1994 | 462053907.972946 MOZAMBIQUE | 1993 | 451438834.778872 MOZAMBIQUE | 1992 | 455939694.613233 PERU | 1998 | 270797018.723306 PERU | 1997 | 473660580.828423 PERU | 1996 | 461969313.364782 PERU | 1995 | 467363910.608805 PERU | 1994 | 459691918.067755 PERU | 1993 | 467347018.781517 PERU | 1992 | 469144992.697353 ROMANIA | 1998 | 271736626.885543 ROMANIA | 1997 | 466380875.402252 ROMANIA | 1996 | 464025778.054498 ROMANIA | 1995 | 460974139.38395 ROMANIA | 1994 | 469198092.72348 ROMANIA | 1993 | 460785302.748139 ROMANIA | 1992 | 464428958.64673 RUSSIA | 1998 | 275355778.964737 RUSSIA | 1997 | 458524619.963395 RUSSIA | 1996 | 468092380.749275 RUSSIA | 1995 | 460094216.836214 RUSSIA | 1994 | 460722134.963266 RUSSIA | 1993 | 459137998.738946 RUSSIA | 1992 | 469448664.107959 SAUDI ARABIA | 1998 | 281359295.172491 SAUDI ARABIA | 1997 | 470201458.865912 SAUDI ARABIA | 1996 | 477758192.542046 SAUDI ARABIA | 1995 | 482194413.550369 SAUDI ARABIA | 1994 | 472842391.291143 SAUDI ARABIA | 1993 | 479694093.701063 SAUDI ARABIA | 1992 | 481821763.27304 UNITED KINGDOM | 1998 | 266214046.243665 UNITED KINGDOM | 1997 | 462252979.01514 UNITED KINGDOM | 1996 | 460889169.632847 UNITED KINGDOM | 1995 | 450597732.545439 UNITED KINGDOM | 1994 | 454333108.405833 UNITED KINGDOM | 1993 | 458459140.325711 UNITED KINGDOM | 1992 | 462959110.72988 UNITED STATES | 1998 | 271371754.488124 UNITED STATES | 1997 | 472479912.375062 UNITED STATES | 1996 | 468678431.023982 UNITED STATES | 1995 | 467789848.51731 UNITED STATES | 1994 | 478220640.406023 UNITED STATES | 1993 | 469028532.847145 UNITED STATES | 1992 | 471570080.180267 VIETNAM | 1998 | 270436147.584213 VIETNAM | 1997 | 464122681.128584 VIETNAM | 1996 | 468108826.176832 VIETNAM | 1995 | 461691510.027752 VIETNAM | 1994 | 461239614.017042 VIETNAM | 1993 | 458128443.527633 VIETNAM | 1992 | 457513662.354346 (175 rows) COMMIT; COMMIT