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 '%green%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271679692.959812 ALGERIA | 1997 | 457435261.442743 ALGERIA | 1996 | 457689620.476572 ALGERIA | 1995 | 461498357.176302 ALGERIA | 1994 | 457162526.0512 ALGERIA | 1993 | 456972051.555932 ALGERIA | 1992 | 460069296.267463 ARGENTINA | 1998 | 278008870.972582 ARGENTINA | 1997 | 468252142.654023 ARGENTINA | 1996 | 474367383.011092 ARGENTINA | 1995 | 468819254.437569 ARGENTINA | 1994 | 467660056.762471 ARGENTINA | 1993 | 470321648.930646 ARGENTINA | 1992 | 471694091.622765 BRAZIL | 1998 | 278413162.616964 BRAZIL | 1997 | 461272643.762 BRAZIL | 1996 | 465915114.259998 BRAZIL | 1995 | 466905829.076182 BRAZIL | 1994 | 467582985.445046 BRAZIL | 1993 | 459341828.979429 BRAZIL | 1992 | 468711716.585579 CANADA | 1998 | 275599362.603594 CANADA | 1997 | 474703397.113647 CANADA | 1996 | 477988852.666478 CANADA | 1995 | 473644277.47668 CANADA | 1994 | 481487381.044819 CANADA | 1993 | 473465416.87251 CANADA | 1992 | 482299852.678469 CHINA | 1998 | 272365549.898693 CHINA | 1997 | 456350410.936584 CHINA | 1996 | 461884115.311697 CHINA | 1995 | 460088904.530548 CHINA | 1994 | 468957956.569416 CHINA | 1993 | 461862853.700512 CHINA | 1992 | 460226614.133193 EGYPT | 1998 | 276191984.588658 EGYPT | 1997 | 469334317.992567 EGYPT | 1996 | 481188684.900325 EGYPT | 1995 | 463644861.246594 EGYPT | 1994 | 475285198.914229 EGYPT | 1993 | 471244267.18771 EGYPT | 1992 | 471551787.214132 ETHIOPIA | 1998 | 267909800.747425 ETHIOPIA | 1997 | 463396506.056258 ETHIOPIA | 1996 | 462651472.971829 ETHIOPIA | 1995 | 463849101.449504 ETHIOPIA | 1994 | 462419153.321617 ETHIOPIA | 1993 | 462022209.70799 ETHIOPIA | 1992 | 467641149.650836 FRANCE | 1998 | 267069535.504 FRANCE | 1997 | 458684941.808125 FRANCE | 1996 | 461730793.688733 FRANCE | 1995 | 466603045.610091 FRANCE | 1994 | 463033972.271028 FRANCE | 1993 | 457412717.961016 FRANCE | 1992 | 464976843.811894 GERMANY | 1998 | 277695158.883823 GERMANY | 1997 | 470671367.684314 GERMANY | 1996 | 461872054.654856 GERMANY | 1995 | 466034669.421231 GERMANY | 1994 | 476625986.352621 GERMANY | 1993 | 471453541.618631 GERMANY | 1992 | 465863019.997232 INDIA | 1998 | 276394655.888601 INDIA | 1997 | 461783653.969917 INDIA | 1996 | 466165487.483616 INDIA | 1995 | 476894281.181219 INDIA | 1994 | 471050360.803705 INDIA | 1993 | 476842592.245318 INDIA | 1992 | 468941940.178265 INDONESIA | 1998 | 264324997.808386 INDONESIA | 1997 | 460603185.395351 INDONESIA | 1996 | 464024003.859357 INDONESIA | 1995 | 456265549.940484 INDONESIA | 1994 | 465688971.684162 INDONESIA | 1993 | 459945928.345358 INDONESIA | 1992 | 460213683.90342 IRAN | 1998 | 277315187.451304 IRAN | 1997 | 461526949.079385 IRAN | 1996 | 465362512.978027 IRAN | 1995 | 471698831.155136 IRAN | 1994 | 475370820.154924 IRAN | 1993 | 469912560.966776 IRAN | 1992 | 465878668.880516 IRAQ | 1998 | 277377721.991557 IRAQ | 1997 | 466745783.941425 IRAQ | 1996 | 480645128.20975 IRAQ | 1995 | 466540175.414602 IRAQ | 1994 | 471380656.011624 IRAQ | 1993 | 473645409.706494 IRAQ | 1992 | 474782341.322275 JAPAN | 1998 | 275047643.857493 JAPAN | 1997 | 468782577.495622 JAPAN | 1996 | 463580594.947168 JAPAN | 1995 | 462791520.021266 JAPAN | 1994 | 476873027.091213 JAPAN | 1993 | 478287612.338776 JAPAN | 1992 | 472663254.902751 JORDAN | 1998 | 271304029.930206 JORDAN | 1997 | 460217427.040464 JORDAN | 1996 | 456812140.405295 JORDAN | 1995 | 464635877.945683 JORDAN | 1994 | 467069802.997001 JORDAN | 1993 | 462893260.076901 JORDAN | 1992 | 463822822.306657 KENYA | 1998 | 278718731.608648 KENYA | 1997 | 477423552.406836 KENYA | 1996 | 482863410.082325 KENYA | 1995 | 474177954.694531 KENYA | 1994 | 472334915.935347 KENYA | 1993 | 475907845.834408 KENYA | 1992 | 476625127.06035 MOROCCO | 1998 | 272264842.489078 MOROCCO | 1997 | 465477804.659183 MOROCCO | 1996 | 469897269.684813 MOROCCO | 1995 | 476001363.650725 MOROCCO | 1994 | 464472064.589968 MOROCCO | 1993 | 463118674.410024 MOROCCO | 1992 | 466666712.620034 MOZAMBIQUE | 1998 | 274139407.814781 MOZAMBIQUE | 1997 | 460798764.207927 MOZAMBIQUE | 1996 | 460431336.629873 MOZAMBIQUE | 1995 | 451904334.242727 MOZAMBIQUE | 1994 | 463351439.296679 MOZAMBIQUE | 1993 | 457401368.792428 MOZAMBIQUE | 1992 | 453342604.768577 PERU | 1998 | 268005555.056068 PERU | 1997 | 462160977.853811 PERU | 1996 | 465550392.071709 PERU | 1995 | 461822515.604121 PERU | 1994 | 466258366.467201 PERU | 1993 | 467560316.287698 PERU | 1992 | 467660037.413898 ROMANIA | 1998 | 269771024.98222 ROMANIA | 1997 | 460770519.880421 ROMANIA | 1996 | 460293156.598341 ROMANIA | 1995 | 461448204.03238 ROMANIA | 1994 | 453027407.58269 ROMANIA | 1993 | 466286459.266541 ROMANIA | 1992 | 469518225.383269 RUSSIA | 1998 | 276038976.002377 RUSSIA | 1997 | 469096349.356724 RUSSIA | 1996 | 464581101.712804 RUSSIA | 1995 | 463930757.097273 RUSSIA | 1994 | 460253116.608961 RUSSIA | 1993 | 462876585.596962 RUSSIA | 1992 | 468707406.152448 SAUDI ARABIA | 1998 | 279654615.28324 SAUDI ARABIA | 1997 | 468263795.786943 SAUDI ARABIA | 1996 | 479058466.504425 SAUDI ARABIA | 1995 | 477571864.569076 SAUDI ARABIA | 1994 | 471471711.662554 SAUDI ARABIA | 1993 | 469103410.999096 SAUDI ARABIA | 1992 | 480427706.796001 UNITED KINGDOM | 1998 | 270129433.532553 UNITED KINGDOM | 1997 | 463886343.189708 UNITED KINGDOM | 1996 | 467951928.422304 UNITED KINGDOM | 1995 | 465704392.716669 UNITED KINGDOM | 1994 | 466663237.156228 UNITED KINGDOM | 1993 | 467455359.846432 UNITED KINGDOM | 1992 | 464884246.03456 UNITED STATES | 1998 | 272205215.149715 UNITED STATES | 1997 | 466788696.593628 UNITED STATES | 1996 | 467188289.019935 UNITED STATES | 1995 | 471376469.676611 UNITED STATES | 1994 | 476912586.221044 UNITED STATES | 1993 | 460775812.280799 UNITED STATES | 1992 | 468602454.123138 VIETNAM | 1998 | 273386711.786553 VIETNAM | 1997 | 474060011.792301 VIETNAM | 1996 | 466482705.060499 VIETNAM | 1995 | 469309570.030173 VIETNAM | 1994 | 478372109.665962 VIETNAM | 1993 | 469849818.219205 VIETNAM | 1992 | 464471087.985604 (175 rows) COMMIT; COMMIT