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 | 271857687.036451 ALGERIA | 1997 | 457257940.694662 ALGERIA | 1996 | 457704809.562298 ALGERIA | 1995 | 461774846.33854 ALGERIA | 1994 | 457234122.021776 ALGERIA | 1993 | 456709971.531621 ALGERIA | 1992 | 460462157.97481 ARGENTINA | 1998 | 278044670.479852 ARGENTINA | 1997 | 468503074.574388 ARGENTINA | 1996 | 474067519.045939 ARGENTINA | 1995 | 469076849.535744 ARGENTINA | 1994 | 467604348.127921 ARGENTINA | 1993 | 470686793.864863 ARGENTINA | 1992 | 471696227.480066 BRAZIL | 1998 | 277996053.444777 BRAZIL | 1997 | 461666444.48532 BRAZIL | 1996 | 466486374.857179 BRAZIL | 1995 | 467159592.788169 BRAZIL | 1994 | 467730897.007387 BRAZIL | 1993 | 459579106.780433 BRAZIL | 1992 | 468810130.032196 CANADA | 1998 | 275616050.46977 CANADA | 1997 | 474603520.466711 CANADA | 1996 | 477459642.663067 CANADA | 1995 | 473892764.899403 CANADA | 1994 | 481453889.027081 CANADA | 1993 | 473136079.41581 CANADA | 1992 | 482292131.787252 CHINA | 1998 | 272029636.359137 CHINA | 1997 | 456528123.327772 CHINA | 1996 | 461974401.609417 CHINA | 1995 | 459852913.429253 CHINA | 1994 | 469338268.379462 CHINA | 1993 | 462511186.997862 CHINA | 1992 | 460417753.432101 EGYPT | 1998 | 276427566.36149 EGYPT | 1997 | 469299152.239484 EGYPT | 1996 | 481166980.311734 EGYPT | 1995 | 464424139.215464 EGYPT | 1994 | 475773451.202048 EGYPT | 1993 | 471062188.144821 EGYPT | 1992 | 471830748.072848 ETHIOPIA | 1998 | 267783287.224246 ETHIOPIA | 1997 | 462853614.777619 ETHIOPIA | 1996 | 462274092.48344 ETHIOPIA | 1995 | 463557546.405643 ETHIOPIA | 1994 | 461956361.66423 ETHIOPIA | 1993 | 461452909.145976 ETHIOPIA | 1992 | 467153092.871771 FRANCE | 1998 | 266960754.932567 FRANCE | 1997 | 458930816.823414 FRANCE | 1996 | 461845147.651435 FRANCE | 1995 | 466820355.349426 FRANCE | 1994 | 462655393.648613 FRANCE | 1993 | 457585359.95792 FRANCE | 1992 | 465368927.178959 GERMANY | 1998 | 278144892.800092 GERMANY | 1997 | 470363538.527842 GERMANY | 1996 | 461471668.458389 GERMANY | 1995 | 465784621.280314 GERMANY | 1994 | 477099970.795884 GERMANY | 1993 | 471740502.608492 GERMANY | 1992 | 466121145.040139 INDIA | 1998 | 276377331.43245 INDIA | 1997 | 462329302.261911 INDIA | 1996 | 466474246.239176 INDIA | 1995 | 476295040.842163 INDIA | 1994 | 471087758.591523 INDIA | 1993 | 476680524.123168 INDIA | 1992 | 469043814.731997 INDONESIA | 1998 | 264081549.076724 INDONESIA | 1997 | 460437817.771832 INDONESIA | 1996 | 464039077.575078 INDONESIA | 1995 | 456352371.693913 INDONESIA | 1994 | 465877635.85336 INDONESIA | 1993 | 459905626.498803 INDONESIA | 1992 | 459337402.910704 IRAN | 1998 | 277229470.037972 IRAN | 1997 | 461689247.520069 IRAN | 1996 | 465378656.615336 IRAN | 1995 | 471266912.317214 IRAN | 1994 | 475315843.051299 IRAN | 1993 | 470137592.81791 IRAN | 1992 | 465989371.052318 IRAQ | 1998 | 277439017.373197 IRAQ | 1997 | 466956218.885093 IRAQ | 1996 | 480540563.50341 IRAQ | 1995 | 466630389.944436 IRAQ | 1994 | 471722146.021744 IRAQ | 1993 | 473609963.540851 IRAQ | 1992 | 474276935.471054 JAPAN | 1998 | 275014389.260647 JAPAN | 1997 | 469300060.749923 JAPAN | 1996 | 463914694.151622 JAPAN | 1995 | 462734158.038511 JAPAN | 1994 | 477391528.17458 JAPAN | 1993 | 478355314.79856 JAPAN | 1992 | 472705967.169704 JORDAN | 1998 | 271375849.74039 JORDAN | 1997 | 460863449.962132 JORDAN | 1996 | 456206801.381588 JORDAN | 1995 | 465350568.686902 JORDAN | 1994 | 467695647.898684 JORDAN | 1993 | 463449244.524478 JORDAN | 1992 | 463616224.112142 KENYA | 1998 | 278101334.035747 KENYA | 1997 | 477514198.380226 KENYA | 1996 | 482897992.172368 KENYA | 1995 | 473864396.290508 KENYA | 1994 | 472451448.075699 KENYA | 1993 | 475434764.924744 KENYA | 1992 | 476229465.612792 MOROCCO | 1998 | 272467121.741015 MOROCCO | 1997 | 465788043.210156 MOROCCO | 1996 | 470220985.353792 MOROCCO | 1995 | 476001581.827219 MOROCCO | 1994 | 464856895.584855 MOROCCO | 1993 | 463718135.939467 MOROCCO | 1992 | 466221774.733878 MOZAMBIQUE | 1998 | 273676998.230372 MOZAMBIQUE | 1997 | 461225587.596879 MOZAMBIQUE | 1996 | 460569562.50279 MOZAMBIQUE | 1995 | 451897031.191306 MOZAMBIQUE | 1994 | 463143841.721671 MOZAMBIQUE | 1993 | 457309258.125152 MOZAMBIQUE | 1992 | 453696561.131918 PERU | 1998 | 267793064.319205 PERU | 1997 | 462471480.088615 PERU | 1996 | 465865144.765382 PERU | 1995 | 461752710.221637 PERU | 1994 | 465962548.494023 PERU | 1993 | 467598167.221162 PERU | 1992 | 467573944.664592 ROMANIA | 1998 | 269740770.417019 ROMANIA | 1997 | 461032857.201473 ROMANIA | 1996 | 460486154.416463 ROMANIA | 1995 | 461410667.101359 ROMANIA | 1994 | 453154470.367723 ROMANIA | 1993 | 466251189.358519 ROMANIA | 1992 | 469437462.183679 RUSSIA | 1998 | 276130584.797592 RUSSIA | 1997 | 469216988.08008 RUSSIA | 1996 | 464599632.790974 RUSSIA | 1995 | 463777569.401626 RUSSIA | 1994 | 460384602.358537 RUSSIA | 1993 | 462458835.47317 RUSSIA | 1992 | 468669388.784499 SAUDI ARABIA | 1998 | 279545649.069647 SAUDI ARABIA | 1997 | 468613028.436834 SAUDI ARABIA | 1996 | 478897287.650167 SAUDI ARABIA | 1995 | 478370125.565917 SAUDI ARABIA | 1994 | 471294404.415291 SAUDI ARABIA | 1993 | 469238403.919718 SAUDI ARABIA | 1992 | 480512196.162584 UNITED KINGDOM | 1998 | 270497386.887389 UNITED KINGDOM | 1997 | 463781840.013114 UNITED KINGDOM | 1996 | 467547664.244729 UNITED KINGDOM | 1995 | 466239544.196057 UNITED KINGDOM | 1994 | 466593329.573758 UNITED KINGDOM | 1993 | 467182791.94087 UNITED KINGDOM | 1992 | 465060143.952625 UNITED STATES | 1998 | 271839264.221988 UNITED STATES | 1997 | 466649049.327004 UNITED STATES | 1996 | 467057912.720903 UNITED STATES | 1995 | 471613115.088875 UNITED STATES | 1994 | 476687157.44154 UNITED STATES | 1993 | 461049628.568174 UNITED STATES | 1992 | 468458743.998374 VIETNAM | 1998 | 273165266.069028 VIETNAM | 1997 | 473890737.342659 VIETNAM | 1996 | 466372365.77457 VIETNAM | 1995 | 469182337.313865 VIETNAM | 1994 | 478374544.677638 VIETNAM | 1993 | 469849434.047816 VIETNAM | 1992 | 464408924.718838 (175 rows) COMMIT; COMMIT