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 '%peru%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267320406.021041 ALGERIA | 1997 | 454799340.587753 ALGERIA | 1996 | 458051095.643716 ALGERIA | 1995 | 458334081.208728 ALGERIA | 1994 | 461031315.081559 ALGERIA | 1993 | 454308071.95079 ALGERIA | 1992 | 457046766.859327 ARGENTINA | 1998 | 275530150.228693 ARGENTINA | 1997 | 468650361.232664 ARGENTINA | 1996 | 478091128.139817 ARGENTINA | 1995 | 473330510.703827 ARGENTINA | 1994 | 473177554.548126 ARGENTINA | 1993 | 463097704.418727 ARGENTINA | 1992 | 470336206.689083 BRAZIL | 1998 | 275521373.151032 BRAZIL | 1997 | 475139411.019153 BRAZIL | 1996 | 476141115.661776 BRAZIL | 1995 | 476372830.378393 BRAZIL | 1994 | 474431353.369914 BRAZIL | 1993 | 470029317.777205 BRAZIL | 1992 | 469966572.320491 CANADA | 1998 | 273557065.521254 CANADA | 1997 | 480354266.510698 CANADA | 1996 | 480626120.878421 CANADA | 1995 | 473842429.230694 CANADA | 1994 | 478006065.390458 CANADA | 1993 | 472692334.988936 CANADA | 1992 | 487143897.444136 CHINA | 1998 | 276152693.931728 CHINA | 1997 | 468116446.616447 CHINA | 1996 | 469873015.347655 CHINA | 1995 | 469291498.393889 CHINA | 1994 | 471896252.561484 CHINA | 1993 | 466118467.119387 CHINA | 1992 | 470816989.893608 EGYPT | 1998 | 274542869.005711 EGYPT | 1997 | 469156898.507906 EGYPT | 1996 | 471472035.275021 EGYPT | 1995 | 470597245.491984 EGYPT | 1994 | 466887865.301324 EGYPT | 1993 | 465403504.111446 EGYPT | 1992 | 470732775.171508 ETHIOPIA | 1998 | 263346257.054105 ETHIOPIA | 1997 | 453283878.401252 ETHIOPIA | 1996 | 456920987.142632 ETHIOPIA | 1995 | 451492407.047661 ETHIOPIA | 1994 | 462651727.249058 ETHIOPIA | 1993 | 457760554.588413 ETHIOPIA | 1992 | 459173840.136345 FRANCE | 1998 | 268422808.355164 FRANCE | 1997 | 462352469.00264 FRANCE | 1996 | 468315880.966152 FRANCE | 1995 | 462369020.754873 FRANCE | 1994 | 467380137.032105 FRANCE | 1993 | 470839985.551974 FRANCE | 1992 | 472419751.691319 GERMANY | 1998 | 277325922.630961 GERMANY | 1997 | 472258197.617238 GERMANY | 1996 | 471489138.001332 GERMANY | 1995 | 474347990.090055 GERMANY | 1994 | 485641107.560487 GERMANY | 1993 | 474652744.316036 GERMANY | 1992 | 470688116.792725 INDIA | 1998 | 278538521.497459 INDIA | 1997 | 472979612.380648 INDIA | 1996 | 486221230.262863 INDIA | 1995 | 475046064.29439 INDIA | 1994 | 473534119.826818 INDIA | 1993 | 478557528.440964 INDIA | 1992 | 480791206.877412 INDONESIA | 1998 | 273006611.110831 INDONESIA | 1997 | 461395518.360173 INDONESIA | 1996 | 457730016.764965 INDONESIA | 1995 | 465476731.889408 INDONESIA | 1994 | 467859320.646261 INDONESIA | 1993 | 460372916.325349 INDONESIA | 1992 | 467827796.488061 IRAN | 1998 | 273020663.488298 IRAN | 1997 | 462005770.802329 IRAN | 1996 | 458392349.724189 IRAN | 1995 | 455784065.656986 IRAN | 1994 | 456481315.631717 IRAN | 1993 | 462015090.83697 IRAN | 1992 | 466671000.679833 IRAQ | 1998 | 277985216.302764 IRAQ | 1997 | 479610396.449978 IRAQ | 1996 | 485247695.111949 IRAQ | 1995 | 476816354.518116 IRAQ | 1994 | 480115496.852279 IRAQ | 1993 | 474383414.805121 IRAQ | 1992 | 467250034.682127 JAPAN | 1998 | 276370778.417702 JAPAN | 1997 | 462277054.856635 JAPAN | 1996 | 471695822.137326 JAPAN | 1995 | 460161539.185147 JAPAN | 1994 | 468494532.866611 JAPAN | 1993 | 467424464.062115 JAPAN | 1992 | 465450729.22766 JORDAN | 1998 | 267909925.485376 JORDAN | 1997 | 449641392.627833 JORDAN | 1996 | 454782515.667566 JORDAN | 1995 | 459594634.020634 JORDAN | 1994 | 456289279.909857 JORDAN | 1993 | 460479517.35615 JORDAN | 1992 | 449542837.156144 KENYA | 1998 | 279931457.003483 KENYA | 1997 | 472056525.683886 KENYA | 1996 | 477689312.11355 KENYA | 1995 | 473172228.297482 KENYA | 1994 | 465731340.988982 KENYA | 1993 | 477048690.553255 KENYA | 1992 | 471472058.508947 MOROCCO | 1998 | 276701064.581017 MOROCCO | 1997 | 471365365.175395 MOROCCO | 1996 | 480106771.343995 MOROCCO | 1995 | 471509253.368657 MOROCCO | 1994 | 477997135.278256 MOROCCO | 1993 | 470739761.522307 MOROCCO | 1992 | 471040286.507742 MOZAMBIQUE | 1998 | 270599048.510812 MOZAMBIQUE | 1997 | 457284003.991287 MOZAMBIQUE | 1996 | 466467037.677663 MOZAMBIQUE | 1995 | 457328975.352985 MOZAMBIQUE | 1994 | 462112964.963917 MOZAMBIQUE | 1993 | 461924298.333156 MOZAMBIQUE | 1992 | 461385056.015248 PERU | 1998 | 279768759.907333 PERU | 1997 | 466025184.087492 PERU | 1996 | 479806804.421743 PERU | 1995 | 475962372.29289 PERU | 1994 | 470635559.371405 PERU | 1993 | 474357381.734706 PERU | 1992 | 467358473.775411 ROMANIA | 1998 | 275431267.505664 ROMANIA | 1997 | 468365919.806998 ROMANIA | 1996 | 468458685.118631 ROMANIA | 1995 | 471802958.851236 ROMANIA | 1994 | 463501329.447959 ROMANIA | 1993 | 463196796.400162 ROMANIA | 1992 | 464066583.381195 RUSSIA | 1998 | 279761119.063202 RUSSIA | 1997 | 469914744.434239 RUSSIA | 1996 | 473016696.626281 RUSSIA | 1995 | 475331812.822456 RUSSIA | 1994 | 473110737.201783 RUSSIA | 1993 | 469627763.086749 RUSSIA | 1992 | 481493207.018383 SAUDI ARABIA | 1998 | 272094578.53409 SAUDI ARABIA | 1997 | 470427373.017418 SAUDI ARABIA | 1996 | 473108402.506577 SAUDI ARABIA | 1995 | 466960499.93648 SAUDI ARABIA | 1994 | 459757601.851604 SAUDI ARABIA | 1993 | 462527058.115814 SAUDI ARABIA | 1992 | 471043502.762086 UNITED KINGDOM | 1998 | 272403302.64312 UNITED KINGDOM | 1997 | 463528271.093764 UNITED KINGDOM | 1996 | 469156032.614793 UNITED KINGDOM | 1995 | 466546569.178407 UNITED KINGDOM | 1994 | 462130884.181627 UNITED KINGDOM | 1993 | 472045136.375026 UNITED KINGDOM | 1992 | 469382217.120823 UNITED STATES | 1998 | 273130665.582766 UNITED STATES | 1997 | 467174281.767388 UNITED STATES | 1996 | 465971602.202552 UNITED STATES | 1995 | 468595142.433843 UNITED STATES | 1994 | 470060278.062938 UNITED STATES | 1993 | 464865773.179747 UNITED STATES | 1992 | 467230610.048289 VIETNAM | 1998 | 275543631.309166 VIETNAM | 1997 | 465703013.23711 VIETNAM | 1996 | 462174825.509161 VIETNAM | 1995 | 464072816.847218 VIETNAM | 1994 | 467428354.493879 VIETNAM | 1993 | 469912115.543805 VIETNAM | 1992 | 460001754.470056 (175 rows) COMMIT; COMMIT