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 | 271363141.617185 ALGERIA | 1997 | 457442738.042016 ALGERIA | 1996 | 457274203.155551 ALGERIA | 1995 | 461619649.934866 ALGERIA | 1994 | 457035164.325673 ALGERIA | 1993 | 456240151.797824 ALGERIA | 1992 | 458957607.541953 ARGENTINA | 1998 | 277966468.479199 ARGENTINA | 1997 | 468181283.442914 ARGENTINA | 1996 | 473840431.136035 ARGENTINA | 1995 | 468488165.427318 ARGENTINA | 1994 | 466808532.94289 ARGENTINA | 1993 | 469089086.997536 ARGENTINA | 1992 | 471160818.541879 BRAZIL | 1998 | 277430388.188454 BRAZIL | 1997 | 462081768.23228 BRAZIL | 1996 | 466362915.860625 BRAZIL | 1995 | 466799574.207901 BRAZIL | 1994 | 467321870.624155 BRAZIL | 1993 | 458702300.37543 BRAZIL | 1992 | 468535862.202062 CANADA | 1998 | 275488808.922696 CANADA | 1997 | 473741538.779057 CANADA | 1996 | 476917652.823731 CANADA | 1995 | 473374350.501144 CANADA | 1994 | 481228686.245795 CANADA | 1993 | 472715478.789178 CANADA | 1992 | 482204058.9322 CHINA | 1998 | 271591748.77452 CHINA | 1997 | 456000216.78977 CHINA | 1996 | 461380667.602637 CHINA | 1995 | 459175567.060082 CHINA | 1994 | 469011161.866746 CHINA | 1993 | 462773523.641201 CHINA | 1992 | 460099962.339671 EGYPT | 1998 | 275856857.277026 EGYPT | 1997 | 468768629.734321 EGYPT | 1996 | 481379758.093263 EGYPT | 1995 | 463385472.59197 EGYPT | 1994 | 474862015.418474 EGYPT | 1993 | 470390472.189221 EGYPT | 1992 | 471506664.740638 ETHIOPIA | 1998 | 267515544.531862 ETHIOPIA | 1997 | 463013147.961555 ETHIOPIA | 1996 | 461510033.279342 ETHIOPIA | 1995 | 463249574.247127 ETHIOPIA | 1994 | 461340842.205021 ETHIOPIA | 1993 | 460636686.029567 ETHIOPIA | 1992 | 467110093.16283 FRANCE | 1998 | 266418340.666536 FRANCE | 1997 | 459421156.854868 FRANCE | 1996 | 461202834.316311 FRANCE | 1995 | 466520348.141132 FRANCE | 1994 | 462287998.99775 FRANCE | 1993 | 456897579.12111 FRANCE | 1992 | 464614701.131395 GERMANY | 1998 | 277815603.992188 GERMANY | 1997 | 469788018.178325 GERMANY | 1996 | 461272924.594095 GERMANY | 1995 | 465287111.184874 GERMANY | 1994 | 476571702.524066 GERMANY | 1993 | 470780581.879657 GERMANY | 1992 | 465414165.581338 INDIA | 1998 | 275811221.02466 INDIA | 1997 | 461600374.80164 INDIA | 1996 | 465882257.552162 INDIA | 1995 | 476106975.62405 INDIA | 1994 | 470967034.172329 INDIA | 1993 | 476171543.665246 INDIA | 1992 | 469336739.707169 INDONESIA | 1998 | 263937488.405979 INDONESIA | 1997 | 459685314.272533 INDONESIA | 1996 | 463638797.591569 INDONESIA | 1995 | 455473373.880208 INDONESIA | 1994 | 465642837.679122 INDONESIA | 1993 | 459805112.946705 INDONESIA | 1992 | 458613154.801296 IRAN | 1998 | 276988533.750218 IRAN | 1997 | 461456932.32801 IRAN | 1996 | 464329505.549949 IRAN | 1995 | 470578347.13394 IRAN | 1994 | 474300217.361443 IRAN | 1993 | 469403721.602891 IRAN | 1992 | 465547428.285962 IRAQ | 1998 | 277365143.585839 IRAQ | 1997 | 466047142.466239 IRAQ | 1996 | 479437247.949357 IRAQ | 1995 | 465581202.041234 IRAQ | 1994 | 471020639.466498 IRAQ | 1993 | 473882966.787892 IRAQ | 1992 | 473746575.705399 JAPAN | 1998 | 274720463.611041 JAPAN | 1997 | 469236578.811335 JAPAN | 1996 | 463300362.229788 JAPAN | 1995 | 462393427.913898 JAPAN | 1994 | 476557019.369713 JAPAN | 1993 | 478129028.173452 JAPAN | 1992 | 472199535.903186 JORDAN | 1998 | 271293265.417358 JORDAN | 1997 | 460059810.422139 JORDAN | 1996 | 455369325.5433 JORDAN | 1995 | 464947813.690826 JORDAN | 1994 | 467157020.70138 JORDAN | 1993 | 463190757.946654 JORDAN | 1992 | 462518011.11019 KENYA | 1998 | 277428547.004473 KENYA | 1997 | 476794387.93648 KENYA | 1996 | 482218539.63745 KENYA | 1995 | 473577210.850423 KENYA | 1994 | 472379772.12485 KENYA | 1993 | 476039352.077777 KENYA | 1992 | 476144179.971647 MOROCCO | 1998 | 272159901.431048 MOROCCO | 1997 | 465227043.272655 MOROCCO | 1996 | 469814099.916132 MOROCCO | 1995 | 474742723.577677 MOROCCO | 1994 | 464074147.832562 MOROCCO | 1993 | 463579468.385849 MOROCCO | 1992 | 466013080.812493 MOZAMBIQUE | 1998 | 273415036.806736 MOZAMBIQUE | 1997 | 460826897.123469 MOZAMBIQUE | 1996 | 459863295.942089 MOZAMBIQUE | 1995 | 452445575.696639 MOZAMBIQUE | 1994 | 462308955.330506 MOZAMBIQUE | 1993 | 456246225.037332 MOZAMBIQUE | 1992 | 453425042.138475 PERU | 1998 | 267223766.730534 PERU | 1997 | 462821876.896428 PERU | 1996 | 465528097.645593 PERU | 1995 | 461109535.841048 PERU | 1994 | 465279092.184622 PERU | 1993 | 466789829.08834 PERU | 1992 | 466418062.554975 ROMANIA | 1998 | 269417199.124485 ROMANIA | 1997 | 460223988.3469 ROMANIA | 1996 | 459989473.061485 ROMANIA | 1995 | 460385144.635386 ROMANIA | 1994 | 452640307.753449 ROMANIA | 1993 | 465745437.434446 ROMANIA | 1992 | 469340730.673614 RUSSIA | 1998 | 275959413.939316 RUSSIA | 1997 | 468569020.48235 RUSSIA | 1996 | 464563055.184532 RUSSIA | 1995 | 462957081.505476 RUSSIA | 1994 | 460544791.158888 RUSSIA | 1993 | 462146120.473112 RUSSIA | 1992 | 468263982.888603 SAUDI ARABIA | 1998 | 278846733.578542 SAUDI ARABIA | 1997 | 468273805.847906 SAUDI ARABIA | 1996 | 478154330.436437 SAUDI ARABIA | 1995 | 477733355.426561 SAUDI ARABIA | 1994 | 471147553.039965 SAUDI ARABIA | 1993 | 468822436.819523 SAUDI ARABIA | 1992 | 480306272.485682 UNITED KINGDOM | 1998 | 269833707.575 UNITED KINGDOM | 1997 | 463455930.954959 UNITED KINGDOM | 1996 | 466736383.411985 UNITED KINGDOM | 1995 | 465772684.091041 UNITED KINGDOM | 1994 | 466484041.101752 UNITED KINGDOM | 1993 | 466485730.368787 UNITED KINGDOM | 1992 | 465020381.708032 UNITED STATES | 1998 | 271364045.43975 UNITED STATES | 1997 | 466576977.86148 UNITED STATES | 1996 | 467046864.63365 UNITED STATES | 1995 | 470988339.069499 UNITED STATES | 1994 | 476307271.985724 UNITED STATES | 1993 | 460672822.725162 UNITED STATES | 1992 | 467938470.668217 VIETNAM | 1998 | 273317159.947133 VIETNAM | 1997 | 473694033.348169 VIETNAM | 1996 | 465889204.627811 VIETNAM | 1995 | 468260483.556029 VIETNAM | 1994 | 478122654.173047 VIETNAM | 1993 | 469473771.725822 VIETNAM | 1992 | 463423947.77207 (175 rows) COMMIT; COMMIT