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.149751 ALGERIA | 1996 | 454871690.19816 ALGERIA | 1995 | 453705152.146065 ALGERIA | 1994 | 457461422.971992 ALGERIA | 1993 | 460745597.563372 ALGERIA | 1992 | 458139489.550849 ARGENTINA | 1998 | 274571298.446793 ARGENTINA | 1997 | 465258947.10055 ARGENTINA | 1996 | 471195448.55249 ARGENTINA | 1995 | 477487280.917081 ARGENTINA | 1994 | 466619004.999145 ARGENTINA | 1993 | 467866368.780226 ARGENTINA | 1992 | 472678216.334697 BRAZIL | 1998 | 270589018.047253 BRAZIL | 1997 | 471090635.639572 BRAZIL | 1996 | 469461463.999245 BRAZIL | 1995 | 478806499.106858 BRAZIL | 1994 | 469626107.273552 BRAZIL | 1993 | 467825359.369633 BRAZIL | 1992 | 465479534.105128 CANADA | 1998 | 269444196.981894 CANADA | 1997 | 470984330.055316 CANADA | 1996 | 473512829.553987 CANADA | 1995 | 469910796.122024 CANADA | 1994 | 475802119.395339 CANADA | 1993 | 463903133.059272 CANADA | 1992 | 474050990.320482 CHINA | 1998 | 275025293.761097 CHINA | 1997 | 476626636.216315 CHINA | 1996 | 471131429.682304 CHINA | 1995 | 465576149.516765 CHINA | 1994 | 476190602.198076 CHINA | 1993 | 479809216.999295 CHINA | 1992 | 474245471.471355 EGYPT | 1998 | 276408946.713449 EGYPT | 1997 | 461272607.905634 EGYPT | 1996 | 463634886.352937 EGYPT | 1995 | 462662712.03376 EGYPT | 1994 | 464266603.287895 EGYPT | 1993 | 461242098.694214 EGYPT | 1992 | 464036329.266798 ETHIOPIA | 1998 | 267182315.474609 ETHIOPIA | 1997 | 464543608.718498 ETHIOPIA | 1996 | 455191897.609704 ETHIOPIA | 1995 | 460703448.43236 ETHIOPIA | 1994 | 461251380.886084 ETHIOPIA | 1993 | 457005568.180453 ETHIOPIA | 1992 | 463765679.41409 FRANCE | 1998 | 273101416.876203 FRANCE | 1997 | 463552935.452694 FRANCE | 1996 | 462210213.428017 FRANCE | 1995 | 464194252.240592 FRANCE | 1994 | 462421011.218355 FRANCE | 1993 | 453828729.27309 FRANCE | 1992 | 463557519.804001 GERMANY | 1998 | 278583558.979228 GERMANY | 1997 | 485123431.450131 GERMANY | 1996 | 477158686.801702 GERMANY | 1995 | 473428009.421937 GERMANY | 1994 | 468344503.384925 GERMANY | 1993 | 470622125.434818 GERMANY | 1992 | 474776326.049657 INDIA | 1998 | 274448352.382481 INDIA | 1997 | 470328726.807733 INDIA | 1996 | 481169549.748304 INDIA | 1995 | 481405415.373639 INDIA | 1994 | 475399268.483975 INDIA | 1993 | 476943606.602869 INDIA | 1992 | 480922845.055244 INDONESIA | 1998 | 278925874.291071 INDONESIA | 1997 | 470931012.191693 INDONESIA | 1996 | 477985230.793932 INDONESIA | 1995 | 471724427.19192 INDONESIA | 1994 | 477416866.851778 INDONESIA | 1993 | 471615095.931565 INDONESIA | 1992 | 472900293.714288 IRAN | 1998 | 271787694.838427 IRAN | 1997 | 471870767.894942 IRAN | 1996 | 463571988.869925 IRAN | 1995 | 464443078.508654 IRAN | 1994 | 471436368.595345 IRAN | 1993 | 468284577.467659 IRAN | 1992 | 473308666.711003 IRAQ | 1998 | 272241369.944503 IRAQ | 1997 | 460666919.518533 IRAQ | 1996 | 469929983.49048 IRAQ | 1995 | 458918609.327546 IRAQ | 1994 | 464964750.979041 IRAQ | 1993 | 464719185.810404 IRAQ | 1992 | 464512593.811687 JAPAN | 1998 | 274555072.082075 JAPAN | 1997 | 469812471.60597 JAPAN | 1996 | 459334026.014324 JAPAN | 1995 | 459342364.735801 JAPAN | 1994 | 468514906.007642 JAPAN | 1993 | 467877987.476181 JAPAN | 1992 | 464247230.440654 JORDAN | 1998 | 269124320.957443 JORDAN | 1997 | 454318763.294609 JORDAN | 1996 | 462967496.118805 JORDAN | 1995 | 463292208.924389 JORDAN | 1994 | 467089556.887341 JORDAN | 1993 | 463313238.119186 JORDAN | 1992 | 463836988.532839 KENYA | 1998 | 272329057.276342 KENYA | 1997 | 476709440.334531 KENYA | 1996 | 471866503.732256 KENYA | 1995 | 476633300.308108 KENYA | 1994 | 471430910.254432 KENYA | 1993 | 476270317.693061 KENYA | 1992 | 472708754.028532 MOROCCO | 1998 | 271469105.819341 MOROCCO | 1997 | 466541532.22359 MOROCCO | 1996 | 454795320.015006 MOROCCO | 1995 | 466374283.35159 MOROCCO | 1994 | 463812499.772594 MOROCCO | 1993 | 469918729.31441 MOROCCO | 1992 | 469883751.567243 MOZAMBIQUE | 1998 | 260768485.365735 MOZAMBIQUE | 1997 | 448112022.467975 MOZAMBIQUE | 1996 | 455772076.625826 MOZAMBIQUE | 1995 | 459481144.348654 MOZAMBIQUE | 1994 | 456542352.515929 MOZAMBIQUE | 1993 | 454205608.33218 MOZAMBIQUE | 1992 | 461434111.283088 PERU | 1998 | 273650921.769959 PERU | 1997 | 465197138.245945 PERU | 1996 | 470727003.453379 PERU | 1995 | 465898716.859774 PERU | 1994 | 463398310.200412 PERU | 1993 | 464792929.10283 PERU | 1992 | 463720609.486524 ROMANIA | 1998 | 274183116.634001 ROMANIA | 1997 | 465941313.144409 ROMANIA | 1996 | 467428787.568992 ROMANIA | 1995 | 469677797.326851 ROMANIA | 1994 | 475109682.368261 ROMANIA | 1993 | 464275810.857659 ROMANIA | 1992 | 473901204.93031 RUSSIA | 1998 | 275716538.628506 RUSSIA | 1997 | 464909330.658314 RUSSIA | 1996 | 471188082.397557 RUSSIA | 1995 | 463368808.727877 RUSSIA | 1994 | 464117607.216333 RUSSIA | 1993 | 467698556.042922 RUSSIA | 1992 | 459940929.556307 SAUDI ARABIA | 1998 | 270765439.048822 SAUDI ARABIA | 1997 | 466145667.004949 SAUDI ARABIA | 1996 | 473745272.272357 SAUDI ARABIA | 1995 | 471097107.802935 SAUDI ARABIA | 1994 | 470835206.565203 SAUDI ARABIA | 1993 | 462538464.071645 SAUDI ARABIA | 1992 | 464581426.015881 UNITED KINGDOM | 1998 | 266530741.883097 UNITED KINGDOM | 1997 | 462547215.63353 UNITED KINGDOM | 1996 | 460049339.588697 UNITED KINGDOM | 1995 | 458657273.756142 UNITED KINGDOM | 1994 | 460591069.135411 UNITED KINGDOM | 1993 | 457354444.788362 UNITED KINGDOM | 1992 | 460820842.019758 UNITED STATES | 1998 | 277284534.785156 UNITED STATES | 1997 | 475419581.511278 UNITED STATES | 1996 | 469992765.89789 UNITED STATES | 1995 | 467662684.39768 UNITED STATES | 1994 | 477537189.273672 UNITED STATES | 1993 | 465261087.100111 UNITED STATES | 1992 | 473322884.48144 VIETNAM | 1998 | 271941410.706627 VIETNAM | 1997 | 459071698.370927 VIETNAM | 1996 | 462129623.463317 VIETNAM | 1995 | 454307961.316006 VIETNAM | 1994 | 454826394.135331 VIETNAM | 1993 | 459459693.993196 VIETNAM | 1992 | 456522203.228955 (175 rows) COMMIT; COMMIT