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 '%cyan%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 273499934.992308 ALGERIA | 1997 | 461617502.02587 ALGERIA | 1996 | 473688070.531129 ALGERIA | 1995 | 463890816.682038 ALGERIA | 1994 | 472313104.063791 ALGERIA | 1993 | 468503418.443589 ALGERIA | 1992 | 466512891.311521 ARGENTINA | 1998 | 278640526.44358 ARGENTINA | 1997 | 468319765.123614 ARGENTINA | 1996 | 474648620.320063 ARGENTINA | 1995 | 468054677.754401 ARGENTINA | 1994 | 470552014.466681 ARGENTINA | 1993 | 472076268.152461 ARGENTINA | 1992 | 469705586.699913 BRAZIL | 1998 | 279126661.902388 BRAZIL | 1997 | 476440867.82191 BRAZIL | 1996 | 474287237.4174 BRAZIL | 1995 | 478316398.846481 BRAZIL | 1994 | 474956764.19447 BRAZIL | 1993 | 482418760.650347 BRAZIL | 1992 | 487624483.713663 CANADA | 1998 | 276582946.900345 CANADA | 1997 | 477358710.867616 CANADA | 1996 | 480500408.554309 CANADA | 1995 | 478945561.0766 CANADA | 1994 | 489671631.950032 CANADA | 1993 | 473869932.168836 CANADA | 1992 | 482725912.977188 CHINA | 1998 | 266155580.423694 CHINA | 1997 | 470192637.77332 CHINA | 1996 | 461747939.364722 CHINA | 1995 | 469773761.480283 CHINA | 1994 | 458656359.812629 CHINA | 1993 | 466599449.809679 CHINA | 1992 | 465945167.87596 EGYPT | 1998 | 275373013.652218 EGYPT | 1997 | 469877318.553849 EGYPT | 1996 | 471398149.209731 EGYPT | 1995 | 465283834.127455 EGYPT | 1994 | 462237713.217927 EGYPT | 1993 | 467076963.373288 EGYPT | 1992 | 472230271.520178 ETHIOPIA | 1998 | 271875633.219666 ETHIOPIA | 1997 | 455003964.859747 ETHIOPIA | 1996 | 458614884.947451 ETHIOPIA | 1995 | 453854854.58677 ETHIOPIA | 1994 | 457471409.571296 ETHIOPIA | 1993 | 456980538.714939 ETHIOPIA | 1992 | 461231881.060396 FRANCE | 1998 | 275753338.787069 FRANCE | 1997 | 462571092.7525 FRANCE | 1996 | 463796384.446049 FRANCE | 1995 | 465015510.321396 FRANCE | 1994 | 463534224.353245 FRANCE | 1993 | 466277424.758787 FRANCE | 1992 | 455720209.287271 GERMANY | 1998 | 280894397.584664 GERMANY | 1997 | 480387170.474025 GERMANY | 1996 | 480429496.498509 GERMANY | 1995 | 482474713.225956 GERMANY | 1994 | 483848620.613202 GERMANY | 1993 | 478032776.564302 GERMANY | 1992 | 480695688.158169 INDIA | 1998 | 274856422.149104 INDIA | 1997 | 484717350.720429 INDIA | 1996 | 484100664.764279 INDIA | 1995 | 474556935.726422 INDIA | 1994 | 483996105.466376 INDIA | 1993 | 479597757.885097 INDIA | 1992 | 490622008.020559 INDONESIA | 1998 | 275629322.26928 INDONESIA | 1997 | 461829127.811646 INDONESIA | 1996 | 461198904.667646 INDONESIA | 1995 | 460404157.338362 INDONESIA | 1994 | 456441373.207995 INDONESIA | 1993 | 455001288.106564 INDONESIA | 1992 | 465490614.612279 IRAN | 1998 | 276503679.252501 IRAN | 1997 | 475966295.038615 IRAN | 1996 | 471613382.91633 IRAN | 1995 | 470387601.693011 IRAN | 1994 | 468578491.496943 IRAN | 1993 | 471888568.939932 IRAN | 1992 | 473293230.533579 IRAQ | 1998 | 280299438.705495 IRAQ | 1997 | 475677338.29821 IRAQ | 1996 | 471122161.925879 IRAQ | 1995 | 476752502.272807 IRAQ | 1994 | 469242183.528208 IRAQ | 1993 | 465194955.939558 IRAQ | 1992 | 479566065.617327 JAPAN | 1998 | 273261634.011138 JAPAN | 1997 | 466351820.433172 JAPAN | 1996 | 466990280.927871 JAPAN | 1995 | 466793227.592143 JAPAN | 1994 | 469111738.75364 JAPAN | 1993 | 467117143.664442 JAPAN | 1992 | 460752627.541741 JORDAN | 1998 | 272188554.064918 JORDAN | 1997 | 462472051.083378 JORDAN | 1996 | 450261726.943353 JORDAN | 1995 | 462162734.949341 JORDAN | 1994 | 461237309.808411 JORDAN | 1993 | 458939892.578781 JORDAN | 1992 | 464998358.998469 KENYA | 1998 | 273998711.547914 KENYA | 1997 | 473707002.06827 KENYA | 1996 | 471240873.583454 KENYA | 1995 | 477469723.491566 KENYA | 1994 | 479719163.758184 KENYA | 1993 | 479691658.599158 KENYA | 1992 | 474607177.345805 MOROCCO | 1998 | 277996049.714481 MOROCCO | 1997 | 467683441.811644 MOROCCO | 1996 | 462735868.733151 MOROCCO | 1995 | 462890391.19632 MOROCCO | 1994 | 471320174.095724 MOROCCO | 1993 | 464541428.978363 MOROCCO | 1992 | 469283218.513569 MOZAMBIQUE | 1998 | 274396887.453308 MOZAMBIQUE | 1997 | 461267467.246975 MOZAMBIQUE | 1996 | 458801526.589828 MOZAMBIQUE | 1995 | 462650637.382351 MOZAMBIQUE | 1994 | 467091674.847493 MOZAMBIQUE | 1993 | 468352399.829291 MOZAMBIQUE | 1992 | 460814540.913784 PERU | 1998 | 278916086.823486 PERU | 1997 | 477799140.382403 PERU | 1996 | 476897798.5373 PERU | 1995 | 476008277.798466 PERU | 1994 | 473817354.699026 PERU | 1993 | 467733880.838977 PERU | 1992 | 473929740.563324 ROMANIA | 1998 | 274876815.287605 ROMANIA | 1997 | 464468003.45544 ROMANIA | 1996 | 468398578.512111 ROMANIA | 1995 | 471211886.931577 ROMANIA | 1994 | 466254609.656685 ROMANIA | 1993 | 458453096.218652 ROMANIA | 1992 | 476391410.065882 RUSSIA | 1998 | 280574201.302516 RUSSIA | 1997 | 470126731.686123 RUSSIA | 1996 | 479801379.375212 RUSSIA | 1995 | 476695453.205381 RUSSIA | 1994 | 481061333.299879 RUSSIA | 1993 | 469133119.012526 RUSSIA | 1992 | 475465435.852221 SAUDI ARABIA | 1998 | 275074232.663519 SAUDI ARABIA | 1997 | 477719227.068558 SAUDI ARABIA | 1996 | 475402690.788891 SAUDI ARABIA | 1995 | 479927555.053734 SAUDI ARABIA | 1994 | 465797463.926669 SAUDI ARABIA | 1993 | 473842008.648446 SAUDI ARABIA | 1992 | 479713843.264176 UNITED KINGDOM | 1998 | 269640863.172516 UNITED KINGDOM | 1997 | 458120270.671893 UNITED KINGDOM | 1996 | 467805874.257944 UNITED KINGDOM | 1995 | 465119298.027928 UNITED KINGDOM | 1994 | 459005148.119028 UNITED KINGDOM | 1993 | 462843424.950369 UNITED KINGDOM | 1992 | 471931015.58927 UNITED STATES | 1998 | 279760706.861078 UNITED STATES | 1997 | 479884080.685091 UNITED STATES | 1996 | 473842947.561359 UNITED STATES | 1995 | 471824394.857498 UNITED STATES | 1994 | 467431689.990663 UNITED STATES | 1993 | 472334524.47211 UNITED STATES | 1992 | 469047341.47195 VIETNAM | 1998 | 271904733.60635 VIETNAM | 1997 | 472061649.799277 VIETNAM | 1996 | 469031777.826519 VIETNAM | 1995 | 472858362.360189 VIETNAM | 1994 | 468398656.938549 VIETNAM | 1993 | 479426250.030484 VIETNAM | 1992 | 476166654.495905 (175 rows) COMMIT; COMMIT