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 '%lime%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267824408.395375 ALGERIA | 1997 | 453049936.712312 ALGERIA | 1996 | 449989757.8209 ALGERIA | 1995 | 453540020.862485 ALGERIA | 1994 | 460039515.542756 ALGERIA | 1993 | 450004819.778379 ALGERIA | 1992 | 459180800.803586 ARGENTINA | 1998 | 274887754.793463 ARGENTINA | 1997 | 468137061.858107 ARGENTINA | 1996 | 466082897.618636 ARGENTINA | 1995 | 467774795.020214 ARGENTINA | 1994 | 463462234.344665 ARGENTINA | 1993 | 467870568.140023 ARGENTINA | 1992 | 471106731.758526 BRAZIL | 1998 | 277414493.559631 BRAZIL | 1997 | 473143436.436747 BRAZIL | 1996 | 474250725.3412 BRAZIL | 1995 | 472833266.104412 BRAZIL | 1994 | 473064582.932481 BRAZIL | 1993 | 481359095.722291 BRAZIL | 1992 | 477454897.984674 CANADA | 1998 | 278198195.359932 CANADA | 1997 | 471515054.829405 CANADA | 1996 | 476018426.439687 CANADA | 1995 | 480977845.776068 CANADA | 1994 | 475989340.247652 CANADA | 1993 | 472245596.846051 CANADA | 1992 | 474762069.873381 CHINA | 1998 | 272853267.425967 CHINA | 1997 | 458374620.622167 CHINA | 1996 | 460039551.938244 CHINA | 1995 | 460918845.502559 CHINA | 1994 | 466626925.299239 CHINA | 1993 | 456029050.219388 CHINA | 1992 | 467965367.890835 EGYPT | 1998 | 280852541.020874 EGYPT | 1997 | 477784991.107325 EGYPT | 1996 | 478668866.560616 EGYPT | 1995 | 476664646.946748 EGYPT | 1994 | 475195909.483313 EGYPT | 1993 | 479927782.970996 EGYPT | 1992 | 478967887.565793 ETHIOPIA | 1998 | 264015979.018597 ETHIOPIA | 1997 | 451024104.866607 ETHIOPIA | 1996 | 450230393.401892 ETHIOPIA | 1995 | 453009486.736429 ETHIOPIA | 1994 | 451168854.86322 ETHIOPIA | 1993 | 451636198.102359 ETHIOPIA | 1992 | 452482866.593402 FRANCE | 1998 | 280862564.195285 FRANCE | 1997 | 462278439.279591 FRANCE | 1996 | 473093729.850318 FRANCE | 1995 | 461682957.784939 FRANCE | 1994 | 469705137.473711 FRANCE | 1993 | 467609288.474213 FRANCE | 1992 | 468723634.006836 GERMANY | 1998 | 267547930.211899 GERMANY | 1997 | 468393016.370607 GERMANY | 1996 | 468491413.087622 GERMANY | 1995 | 465270312.651303 GERMANY | 1994 | 461213038.095636 GERMANY | 1993 | 472217133.185488 GERMANY | 1992 | 466181139.205938 INDIA | 1998 | 271856989.248663 INDIA | 1997 | 468699735.081767 INDIA | 1996 | 484719979.866525 INDIA | 1995 | 469552869.814022 INDIA | 1994 | 476526892.731146 INDIA | 1993 | 481748505.696061 INDIA | 1992 | 479358875.524061 INDONESIA | 1998 | 265187874.697563 INDONESIA | 1997 | 458351995.293068 INDONESIA | 1996 | 465675336.931923 INDONESIA | 1995 | 460539739.21409 INDONESIA | 1994 | 458812057.182897 INDONESIA | 1993 | 465222884.758038 INDONESIA | 1992 | 462658994.607701 IRAN | 1998 | 270302494.808322 IRAN | 1997 | 461395950.463918 IRAN | 1996 | 474303523.536779 IRAN | 1995 | 462185613.327279 IRAN | 1994 | 459860349.852668 IRAN | 1993 | 464959007.820939 IRAN | 1992 | 468464159.563948 IRAQ | 1998 | 271757602.782617 IRAQ | 1997 | 467971456.85131 IRAQ | 1996 | 466685954.868307 IRAQ | 1995 | 460274348.33834 IRAQ | 1994 | 469745927.52732 IRAQ | 1993 | 465238554.083144 IRAQ | 1992 | 467353061.481324 JAPAN | 1998 | 272271869.989512 JAPAN | 1997 | 458015710.110825 JAPAN | 1996 | 468501564.202042 JAPAN | 1995 | 461457309.855306 JAPAN | 1994 | 463733913.030027 JAPAN | 1993 | 468722492.272781 JAPAN | 1992 | 463723749.348845 JORDAN | 1998 | 264804378.060272 JORDAN | 1997 | 462492667.130542 JORDAN | 1996 | 455212862.929978 JORDAN | 1995 | 454754658.243937 JORDAN | 1994 | 454467525.242754 JORDAN | 1993 | 459548891.333005 JORDAN | 1992 | 459514579.442114 KENYA | 1998 | 278106836.888474 KENYA | 1997 | 481380466.299721 KENYA | 1996 | 472815659.650826 KENYA | 1995 | 474341156.814718 KENYA | 1994 | 467892288.8393 KENYA | 1993 | 467132236.83435 KENYA | 1992 | 472436368.806885 MOROCCO | 1998 | 272301049.312866 MOROCCO | 1997 | 463732557.216486 MOROCCO | 1996 | 465225292.715754 MOROCCO | 1995 | 463370546.161151 MOROCCO | 1994 | 463882150.845606 MOROCCO | 1993 | 471490400.258549 MOROCCO | 1992 | 460245729.523876 MOZAMBIQUE | 1998 | 271990246.547774 MOZAMBIQUE | 1997 | 453372369.203126 MOZAMBIQUE | 1996 | 456837849.986962 MOZAMBIQUE | 1995 | 462704753.42063 MOZAMBIQUE | 1994 | 462231904.351086 MOZAMBIQUE | 1993 | 460157899.072406 MOZAMBIQUE | 1992 | 462098671.617143 PERU | 1998 | 271483690.23979 PERU | 1997 | 467916598.174342 PERU | 1996 | 467507574.854805 PERU | 1995 | 463068226.657614 PERU | 1994 | 460465831.484717 PERU | 1993 | 464012995.664069 PERU | 1992 | 468344333.668173 ROMANIA | 1998 | 269714692.945518 ROMANIA | 1997 | 461675695.56274 ROMANIA | 1996 | 471509376.88159 ROMANIA | 1995 | 468148760.792593 ROMANIA | 1994 | 456765319.582656 ROMANIA | 1993 | 471324976.025117 ROMANIA | 1992 | 466769635.576555 RUSSIA | 1998 | 279361383.619937 RUSSIA | 1997 | 480392823.297919 RUSSIA | 1996 | 477834985.852273 RUSSIA | 1995 | 468376175.581899 RUSSIA | 1994 | 474885733.696971 RUSSIA | 1993 | 475843922.815653 RUSSIA | 1992 | 469794983.566304 SAUDI ARABIA | 1998 | 278383238.068933 SAUDI ARABIA | 1997 | 476628856.706155 SAUDI ARABIA | 1996 | 470093244.369821 SAUDI ARABIA | 1995 | 470690746.477525 SAUDI ARABIA | 1994 | 465874566.641689 SAUDI ARABIA | 1993 | 476668589.644173 SAUDI ARABIA | 1992 | 476171077.475878 UNITED KINGDOM | 1998 | 278115732.112529 UNITED KINGDOM | 1997 | 461415100.045098 UNITED KINGDOM | 1996 | 469117431.388387 UNITED KINGDOM | 1995 | 468648707.510396 UNITED KINGDOM | 1994 | 466167107.474198 UNITED KINGDOM | 1993 | 461217330.726258 UNITED KINGDOM | 1992 | 456808750.500383 UNITED STATES | 1998 | 274310965.328656 UNITED STATES | 1997 | 479570730.958981 UNITED STATES | 1996 | 467354848.309905 UNITED STATES | 1995 | 465902926.085523 UNITED STATES | 1994 | 470063042.918616 UNITED STATES | 1993 | 474903139.619522 UNITED STATES | 1992 | 463824308.332947 VIETNAM | 1998 | 276341491.09205 VIETNAM | 1997 | 474509586.922949 VIETNAM | 1996 | 482942859.774561 VIETNAM | 1995 | 470337348.658626 VIETNAM | 1994 | 480172793.668337 VIETNAM | 1993 | 473294418.47054 VIETNAM | 1992 | 471892033.579771 (175 rows) COMMIT; COMMIT