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 '%black%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261787130.326217 ALGERIA | 1997 | 449262615.253142 ALGERIA | 1996 | 450322717.255962 ALGERIA | 1995 | 447603082.080076 ALGERIA | 1994 | 455446565.697396 ALGERIA | 1993 | 452457035.235182 ALGERIA | 1992 | 450104236.050933 ARGENTINA | 1998 | 279652641.060449 ARGENTINA | 1997 | 479345255.675704 ARGENTINA | 1996 | 471657669.181917 ARGENTINA | 1995 | 471264350.795336 ARGENTINA | 1994 | 474227001.416334 ARGENTINA | 1993 | 467814404.965525 ARGENTINA | 1992 | 487111168.339369 BRAZIL | 1998 | 279441731.472561 BRAZIL | 1997 | 475649999.911667 BRAZIL | 1996 | 471850836.587713 BRAZIL | 1995 | 471123904.002992 BRAZIL | 1994 | 470195322.445219 BRAZIL | 1993 | 471606258.337854 BRAZIL | 1992 | 476619808.468066 CANADA | 1998 | 281485368.632663 CANADA | 1997 | 485551020.771845 CANADA | 1996 | 483359891.345554 CANADA | 1995 | 480374677.62051 CANADA | 1994 | 489892931.752564 CANADA | 1993 | 485055669.771798 CANADA | 1992 | 491567627.63489 CHINA | 1998 | 274195279.998849 CHINA | 1997 | 461580301.531866 CHINA | 1996 | 464212995.895651 CHINA | 1995 | 470153353.559957 CHINA | 1994 | 467334086.336174 CHINA | 1993 | 466879785.123653 CHINA | 1992 | 466353501.932609 EGYPT | 1998 | 271540637.693581 EGYPT | 1997 | 460544388.020709 EGYPT | 1996 | 469533499.185507 EGYPT | 1995 | 464331960.747004 EGYPT | 1994 | 463105307.049297 EGYPT | 1993 | 466714781.282654 EGYPT | 1992 | 459708856.383441 ETHIOPIA | 1998 | 267274051.057485 ETHIOPIA | 1997 | 452134078.753753 ETHIOPIA | 1996 | 446011387.869452 ETHIOPIA | 1995 | 454126052.11396 ETHIOPIA | 1994 | 455567361.681003 ETHIOPIA | 1993 | 457141087.543459 ETHIOPIA | 1992 | 455105653.987806 FRANCE | 1998 | 264661840.62724 FRANCE | 1997 | 455753822.250642 FRANCE | 1996 | 467963180.891442 FRANCE | 1995 | 467135689.859399 FRANCE | 1994 | 465541530.769811 FRANCE | 1993 | 473248042.957763 FRANCE | 1992 | 464029215.916931 GERMANY | 1998 | 274950617.595169 GERMANY | 1997 | 477142595.518127 GERMANY | 1996 | 476900589.786194 GERMANY | 1995 | 468580766.478442 GERMANY | 1994 | 468146820.368653 GERMANY | 1993 | 468406466.195879 GERMANY | 1992 | 461554599.942646 INDIA | 1998 | 280516423.084312 INDIA | 1997 | 474489851.321741 INDIA | 1996 | 473798507.181819 INDIA | 1995 | 468856504.328492 INDIA | 1994 | 474478277.149044 INDIA | 1993 | 473691872.156761 INDIA | 1992 | 482567544.667095 INDONESIA | 1998 | 269937801.600505 INDONESIA | 1997 | 464165436.758197 INDONESIA | 1996 | 451065460.406079 INDONESIA | 1995 | 461025932.789966 INDONESIA | 1994 | 463544015.764253 INDONESIA | 1993 | 466666658.353864 INDONESIA | 1992 | 461895563.058899 IRAN | 1998 | 271989960.22958 IRAN | 1997 | 462373271.36009 IRAN | 1996 | 462793756.323821 IRAN | 1995 | 467210325.890563 IRAN | 1994 | 459726663.991477 IRAN | 1993 | 472380301.68005 IRAN | 1992 | 464028028.891152 IRAQ | 1998 | 271264365.031187 IRAQ | 1997 | 473661710.540427 IRAQ | 1996 | 469446739.189887 IRAQ | 1995 | 473184063.128001 IRAQ | 1994 | 477103486.985664 IRAQ | 1993 | 470729380.775063 IRAQ | 1992 | 473532134.143507 JAPAN | 1998 | 276254746.575832 JAPAN | 1997 | 470368177.647306 JAPAN | 1996 | 469655345.103238 JAPAN | 1995 | 474807959.705888 JAPAN | 1994 | 465030846.624061 JAPAN | 1993 | 468045003.261712 JAPAN | 1992 | 471914546.863505 JORDAN | 1998 | 271224536.711519 JORDAN | 1997 | 456894207.538945 JORDAN | 1996 | 467287424.711264 JORDAN | 1995 | 461216559.542506 JORDAN | 1994 | 468295507.459805 JORDAN | 1993 | 463826669.285887 JORDAN | 1992 | 466804509.564018 KENYA | 1998 | 280890877.9176 KENYA | 1997 | 468954064.28147 KENYA | 1996 | 473256497.953729 KENYA | 1995 | 473399980.241737 KENYA | 1994 | 473766023.396396 KENYA | 1993 | 470569030.32568 KENYA | 1992 | 470984793.430477 MOROCCO | 1998 | 276530319.978885 MOROCCO | 1997 | 463244362.832216 MOROCCO | 1996 | 470164892.793212 MOROCCO | 1995 | 467939838.904856 MOROCCO | 1994 | 473502006.715874 MOROCCO | 1993 | 471673340.791597 MOROCCO | 1992 | 465619859.136051 MOZAMBIQUE | 1998 | 276196831.606198 MOZAMBIQUE | 1997 | 459757042.551607 MOZAMBIQUE | 1996 | 456271944.268798 MOZAMBIQUE | 1995 | 461360840.458861 MOZAMBIQUE | 1994 | 462638433.930007 MOZAMBIQUE | 1993 | 455773502.527141 MOZAMBIQUE | 1992 | 458287393.10465 PERU | 1998 | 269962591.189896 PERU | 1997 | 471228385.203977 PERU | 1996 | 463377785.503718 PERU | 1995 | 467675720.717944 PERU | 1994 | 462298488.073629 PERU | 1993 | 466469303.194048 PERU | 1992 | 475180263.793376 ROMANIA | 1998 | 281256507.44166 ROMANIA | 1997 | 463099315.32314 ROMANIA | 1996 | 480637364.139159 ROMANIA | 1995 | 473889043.632667 ROMANIA | 1994 | 474690407.637536 ROMANIA | 1993 | 469448512.401227 ROMANIA | 1992 | 473676464.168937 RUSSIA | 1998 | 283111499.547996 RUSSIA | 1997 | 470555544.534916 RUSSIA | 1996 | 473555825.697476 RUSSIA | 1995 | 479735576.641422 RUSSIA | 1994 | 471632079.696986 RUSSIA | 1993 | 471878408.462311 RUSSIA | 1992 | 476964827.958882 SAUDI ARABIA | 1998 | 270227958.313392 SAUDI ARABIA | 1997 | 463280249.502394 SAUDI ARABIA | 1996 | 473176251.874399 SAUDI ARABIA | 1995 | 470852363.24749 SAUDI ARABIA | 1994 | 464949783.87447 SAUDI ARABIA | 1993 | 467866212.545984 SAUDI ARABIA | 1992 | 464205309.771484 UNITED KINGDOM | 1998 | 272796107.895578 UNITED KINGDOM | 1997 | 465900349.776271 UNITED KINGDOM | 1996 | 459477559.752734 UNITED KINGDOM | 1995 | 463888294.787183 UNITED KINGDOM | 1994 | 462295512.484372 UNITED KINGDOM | 1993 | 463699995.067032 UNITED KINGDOM | 1992 | 458825382.977031 UNITED STATES | 1998 | 275072423.311471 UNITED STATES | 1997 | 468691513.978165 UNITED STATES | 1996 | 466032919.325473 UNITED STATES | 1995 | 469787779.252177 UNITED STATES | 1994 | 479932105.773114 UNITED STATES | 1993 | 474021723.743907 UNITED STATES | 1992 | 468329831.673289 VIETNAM | 1998 | 278761509.252145 VIETNAM | 1997 | 462608464.113257 VIETNAM | 1996 | 472761769.442286 VIETNAM | 1995 | 468772119.711789 VIETNAM | 1994 | 467885470.22826 VIETNAM | 1993 | 470058203.430434 VIETNAM | 1992 | 467544938.076207 (175 rows) COMMIT; COMMIT