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 '%navy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267908783.542682 ALGERIA | 1997 | 460243873.434512 ALGERIA | 1996 | 458771908.781102 ALGERIA | 1995 | 465233044.694902 ALGERIA | 1994 | 453098786.76487 ALGERIA | 1993 | 451026674.582646 ALGERIA | 1992 | 457544055.171548 ARGENTINA | 1998 | 278660704.451681 ARGENTINA | 1997 | 478644530.448181 ARGENTINA | 1996 | 483991504.64642 ARGENTINA | 1995 | 473942886.241266 ARGENTINA | 1994 | 479008501.008504 ARGENTINA | 1993 | 472320332.434849 ARGENTINA | 1992 | 468360193.131577 BRAZIL | 1998 | 269331234.644025 BRAZIL | 1997 | 465346856.390419 BRAZIL | 1996 | 464199718.266262 BRAZIL | 1995 | 464660984.482691 BRAZIL | 1994 | 460524565.295848 BRAZIL | 1993 | 462236052.544308 BRAZIL | 1992 | 461733331.0777 CANADA | 1998 | 276051815.071391 CANADA | 1997 | 472857294.14509 CANADA | 1996 | 467476381.255188 CANADA | 1995 | 468931956.434226 CANADA | 1994 | 478321274.201585 CANADA | 1993 | 475338415.498362 CANADA | 1992 | 473406887.033515 CHINA | 1998 | 265536329.067911 CHINA | 1997 | 458814697.181115 CHINA | 1996 | 448078966.073223 CHINA | 1995 | 459463011.297621 CHINA | 1994 | 460186518.507793 CHINA | 1993 | 459467336.139746 CHINA | 1992 | 462071942.863267 EGYPT | 1998 | 272623308.896335 EGYPT | 1997 | 463179948.251321 EGYPT | 1996 | 459027985.367381 EGYPT | 1995 | 464493951.758397 EGYPT | 1994 | 467237032.764114 EGYPT | 1993 | 468638833.471071 EGYPT | 1992 | 465194432.184663 ETHIOPIA | 1998 | 268026546.161908 ETHIOPIA | 1997 | 451441418.415353 ETHIOPIA | 1996 | 449068135.660836 ETHIOPIA | 1995 | 453165099.552247 ETHIOPIA | 1994 | 455147461.979467 ETHIOPIA | 1993 | 454970157.643807 ETHIOPIA | 1992 | 454630538.827339 FRANCE | 1998 | 264649723.074768 FRANCE | 1997 | 457061931.787425 FRANCE | 1996 | 455906455.007153 FRANCE | 1995 | 457181392.281591 FRANCE | 1994 | 450352586.077985 FRANCE | 1993 | 452359668.738654 FRANCE | 1992 | 455012142.305892 GERMANY | 1998 | 276300831.309112 GERMANY | 1997 | 464287683.582598 GERMANY | 1996 | 471970943.176461 GERMANY | 1995 | 476845262.739954 GERMANY | 1994 | 477666241.120288 GERMANY | 1993 | 472970022.239126 GERMANY | 1992 | 474732648.819256 INDIA | 1998 | 275855052.587174 INDIA | 1997 | 478648858.139692 INDIA | 1996 | 472415680.445397 INDIA | 1995 | 470908111.632403 INDIA | 1994 | 478723101.018485 INDIA | 1993 | 478087784.327451 INDIA | 1992 | 475138204.077626 INDONESIA | 1998 | 265002177.560537 INDONESIA | 1997 | 454344238.778163 INDONESIA | 1996 | 455635720.421026 INDONESIA | 1995 | 450511758.171475 INDONESIA | 1994 | 460505488.48207 INDONESIA | 1993 | 455824210.675856 INDONESIA | 1992 | 450022787.485571 IRAN | 1998 | 266924593.588018 IRAN | 1997 | 465297229.015599 IRAN | 1996 | 463452592.853495 IRAN | 1995 | 461275245.418325 IRAN | 1994 | 463854391.843849 IRAN | 1993 | 464722416.871385 IRAN | 1992 | 462725510.089729 IRAQ | 1998 | 271479400.705822 IRAQ | 1997 | 463157001.244055 IRAQ | 1996 | 465571143.216634 IRAQ | 1995 | 461938297.932252 IRAQ | 1994 | 463871795.88968 IRAQ | 1993 | 467508459.720882 IRAQ | 1992 | 460816126.755219 JAPAN | 1998 | 268159442.175966 JAPAN | 1997 | 463536053.133226 JAPAN | 1996 | 462435020.811543 JAPAN | 1995 | 458276903.156924 JAPAN | 1994 | 458846631.543358 JAPAN | 1993 | 463981922.070749 JAPAN | 1992 | 459944173.426034 JORDAN | 1998 | 272022889.68222 JORDAN | 1997 | 460442815.785639 JORDAN | 1996 | 456472809.905602 JORDAN | 1995 | 459101335.361845 JORDAN | 1994 | 452057586.66926 JORDAN | 1993 | 463825705.434294 JORDAN | 1992 | 460749759.621254 KENYA | 1998 | 279097364.581731 KENYA | 1997 | 475701006.639221 KENYA | 1996 | 475325295.655134 KENYA | 1995 | 477649194.881877 KENYA | 1994 | 467640327.285833 KENYA | 1993 | 472939738.905687 KENYA | 1992 | 462426893.127603 MOROCCO | 1998 | 272903781.285361 MOROCCO | 1997 | 471043146.0385 MOROCCO | 1996 | 464645186.833345 MOROCCO | 1995 | 467432483.843861 MOROCCO | 1994 | 470631298.334975 MOROCCO | 1993 | 456159409.807599 MOROCCO | 1992 | 467642250.769237 MOZAMBIQUE | 1998 | 271273709.189285 MOZAMBIQUE | 1997 | 457753448.984145 MOZAMBIQUE | 1996 | 461349761.203211 MOZAMBIQUE | 1995 | 458810640.255214 MOZAMBIQUE | 1994 | 468353670.165976 MOZAMBIQUE | 1993 | 464315662.58654 MOZAMBIQUE | 1992 | 466840045.063801 PERU | 1998 | 273183565.848047 PERU | 1997 | 467129180.182215 PERU | 1996 | 461605469.989806 PERU | 1995 | 454230929.690213 PERU | 1994 | 466171233.501467 PERU | 1993 | 463428914.651169 PERU | 1992 | 473376965.4261 ROMANIA | 1998 | 271723381.916362 ROMANIA | 1997 | 460569877.286506 ROMANIA | 1996 | 471260807.092906 ROMANIA | 1995 | 470381698.17436 ROMANIA | 1994 | 465293834.911758 ROMANIA | 1993 | 457419346.248265 ROMANIA | 1992 | 467919364.618319 RUSSIA | 1998 | 277221859.099998 RUSSIA | 1997 | 478948423.547492 RUSSIA | 1996 | 477818226.168731 RUSSIA | 1995 | 468652543.321196 RUSSIA | 1994 | 468424278.106629 RUSSIA | 1993 | 475406590.947466 RUSSIA | 1992 | 469432654.273286 SAUDI ARABIA | 1998 | 279259887.092916 SAUDI ARABIA | 1997 | 465117982.37877 SAUDI ARABIA | 1996 | 465966567.783668 SAUDI ARABIA | 1995 | 479113539.323179 SAUDI ARABIA | 1994 | 472174088.401249 SAUDI ARABIA | 1993 | 462538183.992076 SAUDI ARABIA | 1992 | 469932214.890029 UNITED KINGDOM | 1998 | 272041167.990665 UNITED KINGDOM | 1997 | 463915615.428817 UNITED KINGDOM | 1996 | 467287663.760364 UNITED KINGDOM | 1995 | 459136822.373817 UNITED KINGDOM | 1994 | 459676061.084623 UNITED KINGDOM | 1993 | 468722319.394394 UNITED KINGDOM | 1992 | 461748791.654241 UNITED STATES | 1998 | 281199781.241077 UNITED STATES | 1997 | 471954184.288395 UNITED STATES | 1996 | 471209604.92323 UNITED STATES | 1995 | 471273740.403319 UNITED STATES | 1994 | 472972536.647285 UNITED STATES | 1993 | 460396410.73468 UNITED STATES | 1992 | 472244972.408633 VIETNAM | 1998 | 273195722.158759 VIETNAM | 1997 | 454556380.002443 VIETNAM | 1996 | 455199424.979713 VIETNAM | 1995 | 463145024.830151 VIETNAM | 1994 | 456201606.891667 VIETNAM | 1993 | 458136877.387843 VIETNAM | 1992 | 459744109.311897 (175 rows) COMMIT; COMMIT