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 | 271468364.517555 ALGERIA | 1997 | 457111685.431705 ALGERIA | 1996 | 457422542.793814 ALGERIA | 1995 | 461192512.19025 ALGERIA | 1994 | 457020883.475122 ALGERIA | 1993 | 456265775.9883 ALGERIA | 1992 | 460105350.445948 ARGENTINA | 1998 | 277779367.04367 ARGENTINA | 1997 | 467892884.522373 ARGENTINA | 1996 | 473547552.475416 ARGENTINA | 1995 | 468623448.288807 ARGENTINA | 1994 | 467105086.725282 ARGENTINA | 1993 | 469962989.582842 ARGENTINA | 1992 | 471098206.388649 BRAZIL | 1998 | 277674093.564263 BRAZIL | 1997 | 461219785.045723 BRAZIL | 1996 | 465855536.34255 BRAZIL | 1995 | 466797060.474992 BRAZIL | 1994 | 467250792.337521 BRAZIL | 1993 | 459072909.315882 BRAZIL | 1992 | 468267601.782299 CANADA | 1998 | 275460318.901358 CANADA | 1997 | 474261283.252079 CANADA | 1996 | 476992679.660554 CANADA | 1995 | 473334038.070774 CANADA | 1994 | 480882116.504222 CANADA | 1993 | 472507632.148083 CANADA | 1992 | 481865939.722104 CHINA | 1998 | 271901675.386394 CHINA | 1997 | 455924117.716381 CHINA | 1996 | 461494791.184489 CHINA | 1995 | 459449485.80356 CHINA | 1994 | 468823693.039534 CHINA | 1993 | 461940158.143687 CHINA | 1992 | 459879891.916868 EGYPT | 1998 | 276059655.610035 EGYPT | 1997 | 468973649.819571 EGYPT | 1996 | 480798977.939036 EGYPT | 1995 | 463909911.296619 EGYPT | 1994 | 475317504.924283 EGYPT | 1993 | 470763537.568138 EGYPT | 1992 | 471341818.982896 ETHIOPIA | 1998 | 267302287.089282 ETHIOPIA | 1997 | 462554603.692676 ETHIOPIA | 1996 | 461803528.401041 ETHIOPIA | 1995 | 463152725.588897 ETHIOPIA | 1994 | 461588385.487207 ETHIOPIA | 1993 | 461163395.792389 ETHIOPIA | 1992 | 466876291.450984 FRANCE | 1998 | 266638516.554971 FRANCE | 1997 | 458199002.392331 FRANCE | 1996 | 461205286.464041 FRANCE | 1995 | 466255623.01847 FRANCE | 1994 | 462267287.106112 FRANCE | 1993 | 457122791.708913 FRANCE | 1992 | 464869119.562027 GERMANY | 1998 | 277522761.820964 GERMANY | 1997 | 469916407.771618 GERMANY | 1996 | 461235249.583892 GERMANY | 1995 | 465335228.836601 GERMANY | 1994 | 476683368.504163 GERMANY | 1993 | 471265900.411448 GERMANY | 1992 | 465583350.940502 INDIA | 1998 | 276099132.80899 INDIA | 1997 | 461779430.869649 INDIA | 1996 | 465853705.639741 INDIA | 1995 | 475959269.716012 INDIA | 1994 | 470758816.786148 INDIA | 1993 | 476083479.393507 INDIA | 1992 | 468413089.622741 INDONESIA | 1998 | 263883678.886441 INDONESIA | 1997 | 460297221.06205 INDONESIA | 1996 | 463679777.77018 INDONESIA | 1995 | 456032723.254199 INDONESIA | 1994 | 465330963.011875 INDONESIA | 1993 | 459553745.290237 INDONESIA | 1992 | 458782606.625818 IRAN | 1998 | 276908942.12938 IRAN | 1997 | 461133920.188651 IRAN | 1996 | 465127849.163765 IRAN | 1995 | 470889419.764577 IRAN | 1994 | 474796305.63746 IRAN | 1993 | 469843001.705392 IRAN | 1992 | 465661012.078653 IRAQ | 1998 | 277031196.098811 IRAQ | 1997 | 466224640.793248 IRAQ | 1996 | 479974502.301169 IRAQ | 1995 | 466278100.676352 IRAQ | 1994 | 471116406.570051 IRAQ | 1993 | 473149086.99958 IRAQ | 1992 | 473731513.202703 JAPAN | 1998 | 274750775.202272 JAPAN | 1997 | 468636920.552246 JAPAN | 1996 | 463432293.863032 JAPAN | 1995 | 462232196.642579 JAPAN | 1994 | 477114292.916616 JAPAN | 1993 | 477910006.873061 JAPAN | 1992 | 472348375.731528 JORDAN | 1998 | 271078309.372957 JORDAN | 1997 | 460191201.176259 JORDAN | 1996 | 455757209.386414 JORDAN | 1995 | 464741894.992253 JORDAN | 1994 | 467096639.87094 JORDAN | 1993 | 463142025.33476 JORDAN | 1992 | 463208313.296255 KENYA | 1998 | 277763252.621617 KENYA | 1997 | 477156363.367353 KENYA | 1996 | 482205242.264862 KENYA | 1995 | 473439859.850072 KENYA | 1994 | 472025509.29289 KENYA | 1993 | 475266140.333686 KENYA | 1992 | 475730994.382403 MOROCCO | 1998 | 272100587.729769 MOROCCO | 1997 | 465221736.18357 MOROCCO | 1996 | 469793724.538487 MOROCCO | 1995 | 475564942.953832 MOROCCO | 1994 | 464270257.153854 MOROCCO | 1993 | 463294645.06536 MOROCCO | 1992 | 465975941.645001 MOZAMBIQUE | 1998 | 273423369.047364 MOZAMBIQUE | 1997 | 460778128.763555 MOZAMBIQUE | 1996 | 460143921.945234 MOZAMBIQUE | 1995 | 451359679.048832 MOZAMBIQUE | 1994 | 462585109.790285 MOZAMBIQUE | 1993 | 456907352.389204 MOZAMBIQUE | 1992 | 453273935.701004 PERU | 1998 | 267435072.070784 PERU | 1997 | 461965144.674024 PERU | 1996 | 465454734.14114 PERU | 1995 | 461510458.330534 PERU | 1994 | 465589126.511129 PERU | 1993 | 467297246.275264 PERU | 1992 | 467297040.770204 ROMANIA | 1998 | 269660185.397322 ROMANIA | 1997 | 460528567.057695 ROMANIA | 1996 | 459906226.989034 ROMANIA | 1995 | 461079491.976371 ROMANIA | 1994 | 452735825.521898 ROMANIA | 1993 | 465891478.462011 ROMANIA | 1992 | 469009489.836748 RUSSIA | 1998 | 276002311.510736 RUSSIA | 1997 | 468844363.893949 RUSSIA | 1996 | 464055801.086318 RUSSIA | 1995 | 463094574.47271 RUSSIA | 1994 | 459943260.285768 RUSSIA | 1993 | 462084310.284641 RUSSIA | 1992 | 467992549.527197 SAUDI ARABIA | 1998 | 279332635.107368 SAUDI ARABIA | 1997 | 468128801.412763 SAUDI ARABIA | 1996 | 478534273.594683 SAUDI ARABIA | 1995 | 477587749.120999 SAUDI ARABIA | 1994 | 470670447.923391 SAUDI ARABIA | 1993 | 468748048.086905 SAUDI ARABIA | 1992 | 480154921.248659 UNITED KINGDOM | 1998 | 270037213.14596 UNITED KINGDOM | 1997 | 463343511.408521 UNITED KINGDOM | 1996 | 467239524.612493 UNITED KINGDOM | 1995 | 465678591.709393 UNITED KINGDOM | 1994 | 466099888.986167 UNITED KINGDOM | 1993 | 466844410.84986 UNITED KINGDOM | 1992 | 464621146.610398 UNITED STATES | 1998 | 271681685.885091 UNITED STATES | 1997 | 466193151.253675 UNITED STATES | 1996 | 466504433.295624 UNITED STATES | 1995 | 471081038.97241 UNITED STATES | 1994 | 476196182.101749 UNITED STATES | 1993 | 460622712.381333 UNITED STATES | 1992 | 467842147.708331 VIETNAM | 1998 | 272984182.552399 VIETNAM | 1997 | 473288871.419816 VIETNAM | 1996 | 466095541.628808 VIETNAM | 1995 | 468757287.665889 VIETNAM | 1994 | 477933300.518595 VIETNAM | 1993 | 469252581.521859 VIETNAM | 1992 | 463912135.663371 (175 rows) COMMIT; COMMIT