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 '%dim%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271026258.415116 ALGERIA | 1997 | 461613918.646245 ALGERIA | 1996 | 467020945.366778 ALGERIA | 1995 | 455183733.073056 ALGERIA | 1994 | 453961654.347855 ALGERIA | 1993 | 460050834.67227 ALGERIA | 1992 | 456226955.231353 ARGENTINA | 1998 | 274912477.759717 ARGENTINA | 1997 | 476601277.885984 ARGENTINA | 1996 | 469869744.241372 ARGENTINA | 1995 | 474626388.531555 ARGENTINA | 1994 | 471047018.083887 ARGENTINA | 1993 | 467126642.144583 ARGENTINA | 1992 | 469957895.901462 BRAZIL | 1998 | 270662694.463233 BRAZIL | 1997 | 461425224.460073 BRAZIL | 1996 | 463485304.028778 BRAZIL | 1995 | 461910074.675246 BRAZIL | 1994 | 458694060.418206 BRAZIL | 1993 | 465158147.233051 BRAZIL | 1992 | 463133199.713562 CANADA | 1998 | 276170639.17446 CANADA | 1997 | 467454823.115626 CANADA | 1996 | 476110085.185605 CANADA | 1995 | 472520543.188411 CANADA | 1994 | 474284189.759378 CANADA | 1993 | 468613713.412618 CANADA | 1992 | 475970799.239349 CHINA | 1998 | 275513236.968817 CHINA | 1997 | 469249216.832609 CHINA | 1996 | 467296780.515091 CHINA | 1995 | 470413524.993071 CHINA | 1994 | 463204607.457421 CHINA | 1993 | 467226433.034615 CHINA | 1992 | 471623349.055641 EGYPT | 1998 | 277010729.849747 EGYPT | 1997 | 472612844.9443 EGYPT | 1996 | 474078044.10056 EGYPT | 1995 | 462409493.150019 EGYPT | 1994 | 466120490.520617 EGYPT | 1993 | 467302557.213077 EGYPT | 1992 | 468026968.039456 ETHIOPIA | 1998 | 266025618.369946 ETHIOPIA | 1997 | 454713426.128678 ETHIOPIA | 1996 | 454984431.576801 ETHIOPIA | 1995 | 455583484.310577 ETHIOPIA | 1994 | 453023655.028507 ETHIOPIA | 1993 | 458899970.164732 ETHIOPIA | 1992 | 462919267.72355 FRANCE | 1998 | 264761458.413626 FRANCE | 1997 | 459036310.427981 FRANCE | 1996 | 453460546.219744 FRANCE | 1995 | 454878269.192023 FRANCE | 1994 | 456708403.28041 FRANCE | 1993 | 457257433.474569 FRANCE | 1992 | 450780102.008731 GERMANY | 1998 | 280280085.999228 GERMANY | 1997 | 473736293.179876 GERMANY | 1996 | 473811906.640743 GERMANY | 1995 | 475929879.39598 GERMANY | 1994 | 478015480.727202 GERMANY | 1993 | 480370760.437252 GERMANY | 1992 | 476694285.791854 INDIA | 1998 | 270898807.692911 INDIA | 1997 | 481087448.566807 INDIA | 1996 | 478948738.528839 INDIA | 1995 | 477704524.508657 INDIA | 1994 | 471800264.89392 INDIA | 1993 | 471326499.895566 INDIA | 1992 | 477527674.019304 INDONESIA | 1998 | 271496321.583004 INDONESIA | 1997 | 463580143.905322 INDONESIA | 1996 | 472089486.993191 INDONESIA | 1995 | 468373418.41099 INDONESIA | 1994 | 459940843.150018 INDONESIA | 1993 | 472406509.715869 INDONESIA | 1992 | 468890615.817515 IRAN | 1998 | 273386908.161624 IRAN | 1997 | 465369865.528179 IRAN | 1996 | 453196870.136342 IRAN | 1995 | 456571971.327605 IRAN | 1994 | 457127608.304625 IRAN | 1993 | 454053619.24753 IRAN | 1992 | 457555250.725832 IRAQ | 1998 | 274462879.270408 IRAQ | 1997 | 473637911.559194 IRAQ | 1996 | 471374620.376377 IRAQ | 1995 | 474212561.303349 IRAQ | 1994 | 471416360.654562 IRAQ | 1993 | 471959419.819091 IRAQ | 1992 | 475184572.780543 JAPAN | 1998 | 269284152.713844 JAPAN | 1997 | 465575577.737326 JAPAN | 1996 | 464802080.633206 JAPAN | 1995 | 468217872.506592 JAPAN | 1994 | 456144180.732485 JAPAN | 1993 | 463168240.416773 JAPAN | 1992 | 460029058.302381 JORDAN | 1998 | 275563163.504884 JORDAN | 1997 | 459701242.513613 JORDAN | 1996 | 457606025.896521 JORDAN | 1995 | 455889355.604472 JORDAN | 1994 | 455883772.321147 JORDAN | 1993 | 455896861.31147 JORDAN | 1992 | 461135812.589769 KENYA | 1998 | 273557116.683026 KENYA | 1997 | 467238471.57558 KENYA | 1996 | 471885039.209494 KENYA | 1995 | 464769594.625193 KENYA | 1994 | 466235183.432046 KENYA | 1993 | 472958606.143413 KENYA | 1992 | 472482376.533763 MOROCCO | 1998 | 275318267.822306 MOROCCO | 1997 | 469260564.044237 MOROCCO | 1996 | 464144268.574971 MOROCCO | 1995 | 467839433.892361 MOROCCO | 1994 | 469913508.599397 MOROCCO | 1993 | 462372289.299441 MOROCCO | 1992 | 469049447.538123 MOZAMBIQUE | 1998 | 274968928.43652 MOZAMBIQUE | 1997 | 469186103.737807 MOZAMBIQUE | 1996 | 466554628.632228 MOZAMBIQUE | 1995 | 470776478.428157 MOZAMBIQUE | 1994 | 458909821.205757 MOZAMBIQUE | 1993 | 463465371.602 MOZAMBIQUE | 1992 | 459816824.432105 PERU | 1998 | 275807503.234848 PERU | 1997 | 468166922.338154 PERU | 1996 | 460841896.534904 PERU | 1995 | 459142394.459428 PERU | 1994 | 462270749.999598 PERU | 1993 | 464372794.861841 PERU | 1992 | 468263970.575876 ROMANIA | 1998 | 276728017.621739 ROMANIA | 1997 | 466033992.976568 ROMANIA | 1996 | 472773187.601356 ROMANIA | 1995 | 466468562.461628 ROMANIA | 1994 | 471204746.202892 ROMANIA | 1993 | 471168310.779441 ROMANIA | 1992 | 471319117.978128 RUSSIA | 1998 | 277065220.518666 RUSSIA | 1997 | 472309619.244043 RUSSIA | 1996 | 474809980.965193 RUSSIA | 1995 | 476949342.60998 RUSSIA | 1994 | 472575749.493568 RUSSIA | 1993 | 472219516.136577 RUSSIA | 1992 | 481362979.363672 SAUDI ARABIA | 1998 | 270210897.064031 SAUDI ARABIA | 1997 | 458264779.89773 SAUDI ARABIA | 1996 | 461082443.612399 SAUDI ARABIA | 1995 | 461815331.958524 SAUDI ARABIA | 1994 | 461597318.128989 SAUDI ARABIA | 1993 | 466196952.239598 SAUDI ARABIA | 1992 | 474393506.562001 UNITED KINGDOM | 1998 | 272781700.443936 UNITED KINGDOM | 1997 | 463501088.417377 UNITED KINGDOM | 1996 | 463127442.328795 UNITED KINGDOM | 1995 | 462729264.121315 UNITED KINGDOM | 1994 | 465921753.876445 UNITED KINGDOM | 1993 | 461227671.331872 UNITED KINGDOM | 1992 | 462833704.192676 UNITED STATES | 1998 | 283705410.239598 UNITED STATES | 1997 | 474637587.548352 UNITED STATES | 1996 | 477305440.440468 UNITED STATES | 1995 | 474158042.506749 UNITED STATES | 1994 | 474934944.611931 UNITED STATES | 1993 | 481144706.367725 UNITED STATES | 1992 | 484988079.447597 VIETNAM | 1998 | 274946455.996609 VIETNAM | 1997 | 463977868.332242 VIETNAM | 1996 | 469949420.083393 VIETNAM | 1995 | 465938343.704878 VIETNAM | 1994 | 465480836.240179 VIETNAM | 1993 | 475044258.408122 VIETNAM | 1992 | 464980256.48696 (175 rows) COMMIT; COMMIT