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 '%forest%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264192356.699034 ALGERIA | 1997 | 461995307.149749 ALGERIA | 1996 | 454871690.198161 ALGERIA | 1995 | 453705152.146065 ALGERIA | 1994 | 457461422.971991 ALGERIA | 1993 | 460745597.563374 ALGERIA | 1992 | 458139489.55085 ARGENTINA | 1998 | 274571298.446793 ARGENTINA | 1997 | 465258947.10055 ARGENTINA | 1996 | 471195448.552492 ARGENTINA | 1995 | 477487280.917081 ARGENTINA | 1994 | 466619004.999146 ARGENTINA | 1993 | 467866368.780226 ARGENTINA | 1992 | 472678216.334696 BRAZIL | 1998 | 270589018.047253 BRAZIL | 1997 | 471090635.639573 BRAZIL | 1996 | 469461463.999248 BRAZIL | 1995 | 478806499.106861 BRAZIL | 1994 | 469626107.273552 BRAZIL | 1993 | 467825359.369636 BRAZIL | 1992 | 465479534.105133 CANADA | 1998 | 269444196.981894 CANADA | 1997 | 470984330.055318 CANADA | 1996 | 473512829.553986 CANADA | 1995 | 469910796.122025 CANADA | 1994 | 475802119.395341 CANADA | 1993 | 463903133.059272 CANADA | 1992 | 474050990.320483 CHINA | 1998 | 275025293.761097 CHINA | 1997 | 476626636.216319 CHINA | 1996 | 471131429.682307 CHINA | 1995 | 465576149.516766 CHINA | 1994 | 476190602.198075 CHINA | 1993 | 479809216.999296 CHINA | 1992 | 474245471.471354 EGYPT | 1998 | 276408946.713449 EGYPT | 1997 | 461272607.905634 EGYPT | 1996 | 463634886.352939 EGYPT | 1995 | 462662712.03376 EGYPT | 1994 | 464266603.287894 EGYPT | 1993 | 461242098.694213 EGYPT | 1992 | 464036329.266797 ETHIOPIA | 1998 | 267182315.474608 ETHIOPIA | 1997 | 464543608.718497 ETHIOPIA | 1996 | 455191897.609704 ETHIOPIA | 1995 | 460703448.432362 ETHIOPIA | 1994 | 461251380.886086 ETHIOPIA | 1993 | 457005568.180458 ETHIOPIA | 1992 | 463765679.41409 FRANCE | 1998 | 273101416.876203 FRANCE | 1997 | 463552935.452693 FRANCE | 1996 | 462210213.428017 FRANCE | 1995 | 464194252.240593 FRANCE | 1994 | 462421011.218355 FRANCE | 1993 | 453828729.27309 FRANCE | 1992 | 463557519.804003 GERMANY | 1998 | 278583558.979227 GERMANY | 1997 | 485123431.450132 GERMANY | 1996 | 477158686.801696 GERMANY | 1995 | 473428009.421938 GERMANY | 1994 | 468344503.384925 GERMANY | 1993 | 470622125.434817 GERMANY | 1992 | 474776326.049658 INDIA | 1998 | 274448352.382482 INDIA | 1997 | 470328726.807732 INDIA | 1996 | 481169549.748302 INDIA | 1995 | 481405415.373639 INDIA | 1994 | 475399268.483973 INDIA | 1993 | 476943606.602871 INDIA | 1992 | 480922845.055244 INDONESIA | 1998 | 278925874.29107 INDONESIA | 1997 | 470931012.191696 INDONESIA | 1996 | 477985230.79393 INDONESIA | 1995 | 471724427.19192 INDONESIA | 1994 | 477416866.851781 INDONESIA | 1993 | 471615095.931565 INDONESIA | 1992 | 472900293.714286 IRAN | 1998 | 271787694.838427 IRAN | 1997 | 471870767.894941 IRAN | 1996 | 463571988.869924 IRAN | 1995 | 464443078.508656 IRAN | 1994 | 471436368.595345 IRAN | 1993 | 468284577.46766 IRAN | 1992 | 473308666.711004 IRAQ | 1998 | 272241369.944503 IRAQ | 1997 | 460666919.518534 IRAQ | 1996 | 469929983.490482 IRAQ | 1995 | 458918609.327546 IRAQ | 1994 | 464964750.979043 IRAQ | 1993 | 464719185.810401 IRAQ | 1992 | 464512593.811687 JAPAN | 1998 | 274555072.082076 JAPAN | 1997 | 469812471.60597 JAPAN | 1996 | 459334026.014323 JAPAN | 1995 | 459342364.735796 JAPAN | 1994 | 468514906.007643 JAPAN | 1993 | 467877987.476181 JAPAN | 1992 | 464247230.440654 JORDAN | 1998 | 269124320.957444 JORDAN | 1997 | 454318763.29461 JORDAN | 1996 | 462967496.118806 JORDAN | 1995 | 463292208.924389 JORDAN | 1994 | 467089556.887341 JORDAN | 1993 | 463313238.119186 JORDAN | 1992 | 463836988.532838 KENYA | 1998 | 272329057.276342 KENYA | 1997 | 476709440.334529 KENYA | 1996 | 471866503.732256 KENYA | 1995 | 476633300.30811 KENYA | 1994 | 471430910.254433 KENYA | 1993 | 476270317.693056 KENYA | 1992 | 472708754.028527 MOROCCO | 1998 | 271469105.819342 MOROCCO | 1997 | 466541532.223592 MOROCCO | 1996 | 454795320.015006 MOROCCO | 1995 | 466374283.351591 MOROCCO | 1994 | 463812499.772596 MOROCCO | 1993 | 469918729.314409 MOROCCO | 1992 | 469883751.567244 MOZAMBIQUE | 1998 | 260768485.365734 MOZAMBIQUE | 1997 | 448112022.467976 MOZAMBIQUE | 1996 | 455772076.625826 MOZAMBIQUE | 1995 | 459481144.348656 MOZAMBIQUE | 1994 | 456542352.515928 MOZAMBIQUE | 1993 | 454205608.332178 MOZAMBIQUE | 1992 | 461434111.283089 PERU | 1998 | 273650921.769958 PERU | 1997 | 465197138.245944 PERU | 1996 | 470727003.453382 PERU | 1995 | 465898716.859773 PERU | 1994 | 463398310.200409 PERU | 1993 | 464792929.102832 PERU | 1992 | 463720609.48652 ROMANIA | 1998 | 274183116.634001 ROMANIA | 1997 | 465941313.144411 ROMANIA | 1996 | 467428787.568991 ROMANIA | 1995 | 469677797.326851 ROMANIA | 1994 | 475109682.36826 ROMANIA | 1993 | 464275810.857659 ROMANIA | 1992 | 473901204.930306 RUSSIA | 1998 | 275716538.628505 RUSSIA | 1997 | 464909330.658315 RUSSIA | 1996 | 471188082.397556 RUSSIA | 1995 | 463368808.727874 RUSSIA | 1994 | 464117607.216334 RUSSIA | 1993 | 467698556.042922 RUSSIA | 1992 | 459940929.556309 SAUDI ARABIA | 1998 | 270765439.048822 SAUDI ARABIA | 1997 | 466145667.004946 SAUDI ARABIA | 1996 | 473745272.272355 SAUDI ARABIA | 1995 | 471097107.802934 SAUDI ARABIA | 1994 | 470835206.565204 SAUDI ARABIA | 1993 | 462538464.071648 SAUDI ARABIA | 1992 | 464581426.01588 UNITED KINGDOM | 1998 | 266530741.883097 UNITED KINGDOM | 1997 | 462547215.63353 UNITED KINGDOM | 1996 | 460049339.588696 UNITED KINGDOM | 1995 | 458657273.756143 UNITED KINGDOM | 1994 | 460591069.135409 UNITED KINGDOM | 1993 | 457354444.788362 UNITED KINGDOM | 1992 | 460820842.01976 UNITED STATES | 1998 | 277284534.785154 UNITED STATES | 1997 | 475419581.511279 UNITED STATES | 1996 | 469992765.89789 UNITED STATES | 1995 | 467662684.397679 UNITED STATES | 1994 | 477537189.273676 UNITED STATES | 1993 | 465261087.100109 UNITED STATES | 1992 | 473322884.481442 VIETNAM | 1998 | 271941410.706627 VIETNAM | 1997 | 459071698.370927 VIETNAM | 1996 | 462129623.463317 VIETNAM | 1995 | 454307961.316007 VIETNAM | 1994 | 454826394.135329 VIETNAM | 1993 | 459459693.993198 VIETNAM | 1992 | 456522203.228954 (175 rows) COMMIT; COMMIT