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 '%papaya%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263242670.950025 ALGERIA | 1997 | 446674980.77336 ALGERIA | 1996 | 449469976.280371 ALGERIA | 1995 | 450854965.83766 ALGERIA | 1994 | 450210907.277119 ALGERIA | 1993 | 448577253.973217 ALGERIA | 1992 | 460122467.455933 ARGENTINA | 1998 | 275769771.137469 ARGENTINA | 1997 | 465701422.129681 ARGENTINA | 1996 | 469531915.18678 ARGENTINA | 1995 | 462517775.211676 ARGENTINA | 1994 | 458640313.219636 ARGENTINA | 1993 | 460769241.23662 ARGENTINA | 1992 | 474154810.766337 BRAZIL | 1998 | 274115451.141839 BRAZIL | 1997 | 464462016.393967 BRAZIL | 1996 | 466121603.962782 BRAZIL | 1995 | 467368898.867521 BRAZIL | 1994 | 473582778.194458 BRAZIL | 1993 | 469608609.002197 BRAZIL | 1992 | 465511540.327538 CANADA | 1998 | 279588420.088381 CANADA | 1997 | 476248860.690912 CANADA | 1996 | 472159768.784671 CANADA | 1995 | 477871381.338366 CANADA | 1994 | 479810641.094642 CANADA | 1993 | 481353576.590592 CANADA | 1992 | 475570869.702036 CHINA | 1998 | 276026158.302027 CHINA | 1997 | 469285550.084171 CHINA | 1996 | 468908999.744461 CHINA | 1995 | 475284002.872402 CHINA | 1994 | 468596831.538392 CHINA | 1993 | 466593156.69425 CHINA | 1992 | 474840204.883924 EGYPT | 1998 | 268012752.668679 EGYPT | 1997 | 463583821.402737 EGYPT | 1996 | 464120530.260361 EGYPT | 1995 | 457869076.839224 EGYPT | 1994 | 462801156.313913 EGYPT | 1993 | 460658420.947394 EGYPT | 1992 | 461691501.230225 ETHIOPIA | 1998 | 267126823.607716 ETHIOPIA | 1997 | 454650962.555676 ETHIOPIA | 1996 | 448325786.34727 ETHIOPIA | 1995 | 456746086.640122 ETHIOPIA | 1994 | 461092687.63817 ETHIOPIA | 1993 | 447956756.388425 ETHIOPIA | 1992 | 458458164.449386 FRANCE | 1998 | 268978208.883697 FRANCE | 1997 | 460588371.783339 FRANCE | 1996 | 465930114.54095 FRANCE | 1995 | 464930394.546161 FRANCE | 1994 | 450982258.245009 FRANCE | 1993 | 459848563.098876 FRANCE | 1992 | 459046483.838163 GERMANY | 1998 | 277763200.28674 GERMANY | 1997 | 472943697.059296 GERMANY | 1996 | 474919105.005669 GERMANY | 1995 | 478076070.763927 GERMANY | 1994 | 472823777.687579 GERMANY | 1993 | 478730189.589863 GERMANY | 1992 | 472472056.434875 INDIA | 1998 | 274327657.281508 INDIA | 1997 | 471969117.284789 INDIA | 1996 | 468379870.868796 INDIA | 1995 | 467864240.404123 INDIA | 1994 | 473826122.449894 INDIA | 1993 | 466889473.580162 INDIA | 1992 | 464235411.027725 INDONESIA | 1998 | 271411993.732726 INDONESIA | 1997 | 465993169.782361 INDONESIA | 1996 | 468695704.917001 INDONESIA | 1995 | 465528756.138487 INDONESIA | 1994 | 458920401.206142 INDONESIA | 1993 | 476190429.278994 INDONESIA | 1992 | 470688545.573306 IRAN | 1998 | 273711528.664269 IRAN | 1997 | 464522320.760009 IRAN | 1996 | 473893556.564859 IRAN | 1995 | 463640552.263022 IRAN | 1994 | 472024734.999239 IRAN | 1993 | 473330462.974401 IRAN | 1992 | 476457221.617248 IRAQ | 1998 | 276703326.177571 IRAQ | 1997 | 469537674.543976 IRAQ | 1996 | 469630386.685329 IRAQ | 1995 | 467174887.212564 IRAQ | 1994 | 480515198.547808 IRAQ | 1993 | 476100296.612333 IRAQ | 1992 | 469223626.692742 JAPAN | 1998 | 270923698.354236 JAPAN | 1997 | 462921054.977901 JAPAN | 1996 | 473378445.132041 JAPAN | 1995 | 472239685.058551 JAPAN | 1994 | 465779648.269473 JAPAN | 1993 | 470483922.577305 JAPAN | 1992 | 468133166.592862 JORDAN | 1998 | 268062174.202994 JORDAN | 1997 | 459619687.591072 JORDAN | 1996 | 460072427.536828 JORDAN | 1995 | 448873823.975667 JORDAN | 1994 | 458960298.157851 JORDAN | 1993 | 461755171.859661 JORDAN | 1992 | 455540875.091268 KENYA | 1998 | 270484492.095415 KENYA | 1997 | 475441112.652157 KENYA | 1996 | 475590186.174508 KENYA | 1995 | 476940234.276254 KENYA | 1994 | 476838482.988381 KENYA | 1993 | 469938098.203969 KENYA | 1992 | 469590999.314107 MOROCCO | 1998 | 275132330.942315 MOROCCO | 1997 | 459940045.854671 MOROCCO | 1996 | 462495376.957473 MOROCCO | 1995 | 467441352.787164 MOROCCO | 1994 | 462382893.496552 MOROCCO | 1993 | 466944229.964635 MOROCCO | 1992 | 472713519.812204 MOZAMBIQUE | 1998 | 261442814.710353 MOZAMBIQUE | 1997 | 446729314.661493 MOZAMBIQUE | 1996 | 456060818.773956 MOZAMBIQUE | 1995 | 462019791.113756 MOZAMBIQUE | 1994 | 450342747.281645 MOZAMBIQUE | 1993 | 455998202.603378 MOZAMBIQUE | 1992 | 448144688.693946 PERU | 1998 | 275097288.015178 PERU | 1997 | 460927082.800904 PERU | 1996 | 464293800.728662 PERU | 1995 | 460821339.761814 PERU | 1994 | 467827895.802363 PERU | 1993 | 458859217.528808 PERU | 1992 | 456904418.367477 ROMANIA | 1998 | 273618800.481019 ROMANIA | 1997 | 460717398.4101 ROMANIA | 1996 | 466524191.88104 ROMANIA | 1995 | 466293536.556024 ROMANIA | 1994 | 462968130.522945 ROMANIA | 1993 | 459418919.352571 ROMANIA | 1992 | 464129094.07541 RUSSIA | 1998 | 275445203.248238 RUSSIA | 1997 | 479613577.049824 RUSSIA | 1996 | 476355674.502762 RUSSIA | 1995 | 467682118.056297 RUSSIA | 1994 | 469756972.674473 RUSSIA | 1993 | 473830394.327916 RUSSIA | 1992 | 475478984.802724 SAUDI ARABIA | 1998 | 277515261.813276 SAUDI ARABIA | 1997 | 473056339.596202 SAUDI ARABIA | 1996 | 477934480.140419 SAUDI ARABIA | 1995 | 469534277.879458 SAUDI ARABIA | 1994 | 480882980.056597 SAUDI ARABIA | 1993 | 471106358.185257 SAUDI ARABIA | 1992 | 471909923.76014 UNITED KINGDOM | 1998 | 280502932.065682 UNITED KINGDOM | 1997 | 466988902.144771 UNITED KINGDOM | 1996 | 464698159.093934 UNITED KINGDOM | 1995 | 470032705.110977 UNITED KINGDOM | 1994 | 462556847.469479 UNITED KINGDOM | 1993 | 472311791.410582 UNITED KINGDOM | 1992 | 464052632.769934 UNITED STATES | 1998 | 272849907.928412 UNITED STATES | 1997 | 475617966.430186 UNITED STATES | 1996 | 471740954.525271 UNITED STATES | 1995 | 477212611.754419 UNITED STATES | 1994 | 473795465.373875 UNITED STATES | 1993 | 476757650.702037 UNITED STATES | 1992 | 468700085.703102 VIETNAM | 1998 | 274258736.969196 VIETNAM | 1997 | 469386886.011934 VIETNAM | 1996 | 470356756.712835 VIETNAM | 1995 | 465885307.836896 VIETNAM | 1994 | 464106678.145542 VIETNAM | 1993 | 474275101.33298 VIETNAM | 1992 | 470053520.239468 (175 rows) COMMIT; COMMIT