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 '%coral%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271914491.927556 ALGERIA | 1997 | 462726528.023924 ALGERIA | 1996 | 470734434.998792 ALGERIA | 1995 | 470322016.761993 ALGERIA | 1994 | 468125063.152062 ALGERIA | 1993 | 470134083.628738 ALGERIA | 1992 | 457851438.602355 ARGENTINA | 1998 | 275939418.174827 ARGENTINA | 1997 | 459774040.594586 ARGENTINA | 1996 | 470342874.303807 ARGENTINA | 1995 | 467877301.202427 ARGENTINA | 1994 | 470830575.333914 ARGENTINA | 1993 | 472610519.593825 ARGENTINA | 1992 | 469632007.242429 BRAZIL | 1998 | 275721071.575504 BRAZIL | 1997 | 468161751.437945 BRAZIL | 1996 | 471858699.699473 BRAZIL | 1995 | 469017196.32144 BRAZIL | 1994 | 463118669.454847 BRAZIL | 1993 | 467888441.205449 BRAZIL | 1992 | 459723612.0048 CANADA | 1998 | 280961182.94425 CANADA | 1997 | 470674982.707189 CANADA | 1996 | 473730391.082388 CANADA | 1995 | 475414027.213979 CANADA | 1994 | 484256607.324936 CANADA | 1993 | 472102737.302949 CANADA | 1992 | 472725389.112735 CHINA | 1998 | 272727819.238004 CHINA | 1997 | 461721450.697684 CHINA | 1996 | 457104025.410498 CHINA | 1995 | 460274230.792289 CHINA | 1994 | 454316563.349441 CHINA | 1993 | 454840040.981011 CHINA | 1992 | 470800208.66847 EGYPT | 1998 | 277614638.574874 EGYPT | 1997 | 467149918.466926 EGYPT | 1996 | 477481930.183941 EGYPT | 1995 | 464467179.097911 EGYPT | 1994 | 465333899.284149 EGYPT | 1993 | 471336686.434584 EGYPT | 1992 | 465845443.688483 ETHIOPIA | 1998 | 258917205.356279 ETHIOPIA | 1997 | 453922737.67051 ETHIOPIA | 1996 | 454703488.403019 ETHIOPIA | 1995 | 462092391.847995 ETHIOPIA | 1994 | 458216640.627076 ETHIOPIA | 1993 | 452105133.933286 ETHIOPIA | 1992 | 461888056.814261 FRANCE | 1998 | 269403169.699862 FRANCE | 1997 | 461129803.777745 FRANCE | 1996 | 457488652.056706 FRANCE | 1995 | 461687423.315368 FRANCE | 1994 | 461928104.762189 FRANCE | 1993 | 463219998.626384 FRANCE | 1992 | 456299153.481231 GERMANY | 1998 | 272446061.93696 GERMANY | 1997 | 476626348.260878 GERMANY | 1996 | 464346311.102399 GERMANY | 1995 | 469337834.272693 GERMANY | 1994 | 476345765.073884 GERMANY | 1993 | 478337729.050092 GERMANY | 1992 | 468111738.046102 INDIA | 1998 | 278580367.487448 INDIA | 1997 | 478598339.079837 INDIA | 1996 | 479872961.111129 INDIA | 1995 | 479741345.39036 INDIA | 1994 | 476362486.155621 INDIA | 1993 | 478926942.315611 INDIA | 1992 | 476541776.993159 INDONESIA | 1998 | 270319696.616492 INDONESIA | 1997 | 462535410.176658 INDONESIA | 1996 | 467202193.834238 INDONESIA | 1995 | 466659674.401408 INDONESIA | 1994 | 465204989.054782 INDONESIA | 1993 | 462362518.742918 INDONESIA | 1992 | 471764882.345406 IRAN | 1998 | 279477489.142625 IRAN | 1997 | 476363447.378575 IRAN | 1996 | 474238634.991119 IRAN | 1995 | 468997049.198402 IRAN | 1994 | 464846032.700396 IRAN | 1993 | 465171946.044464 IRAN | 1992 | 469021852.084086 IRAQ | 1998 | 283944798.839749 IRAQ | 1997 | 472474963.544568 IRAQ | 1996 | 472763478.667662 IRAQ | 1995 | 474965065.422582 IRAQ | 1994 | 473423341.650411 IRAQ | 1993 | 471375776.340252 IRAQ | 1992 | 469787265.487826 JAPAN | 1998 | 271826642.875706 JAPAN | 1997 | 468108078.579833 JAPAN | 1996 | 468749225.622291 JAPAN | 1995 | 464446471.681762 JAPAN | 1994 | 475068023.83008 JAPAN | 1993 | 463437323.892998 JAPAN | 1992 | 461956019.730125 JORDAN | 1998 | 268833940.791641 JORDAN | 1997 | 460664913.491411 JORDAN | 1996 | 450707677.031836 JORDAN | 1995 | 456218367.033007 JORDAN | 1994 | 465526519.567902 JORDAN | 1993 | 467402011.445428 JORDAN | 1992 | 460597840.256433 KENYA | 1998 | 277568203.034731 KENYA | 1997 | 477727091.753598 KENYA | 1996 | 469160983.737044 KENYA | 1995 | 481068953.387942 KENYA | 1994 | 468910331.446443 KENYA | 1993 | 475330576.408161 KENYA | 1992 | 478212753.080001 MOROCCO | 1998 | 279187665.757218 MOROCCO | 1997 | 477320036.075726 MOROCCO | 1996 | 473335500.457551 MOROCCO | 1995 | 466117045.992484 MOROCCO | 1994 | 462236937.117108 MOROCCO | 1993 | 472583084.975346 MOROCCO | 1992 | 478020608.479216 MOZAMBIQUE | 1998 | 268968208.995664 MOZAMBIQUE | 1997 | 465714236.272139 MOZAMBIQUE | 1996 | 463932399.275719 MOZAMBIQUE | 1995 | 466251632.199872 MOZAMBIQUE | 1994 | 462086319.512514 MOZAMBIQUE | 1993 | 455594211.304201 MOZAMBIQUE | 1992 | 465220561.996541 PERU | 1998 | 278166533.112843 PERU | 1997 | 474314132.802971 PERU | 1996 | 468550136.019819 PERU | 1995 | 470971156.54851 PERU | 1994 | 467242489.848974 PERU | 1993 | 468000642.791377 PERU | 1992 | 468530010.260068 ROMANIA | 1998 | 272806360.570641 ROMANIA | 1997 | 457479880.483169 ROMANIA | 1996 | 471461871.547468 ROMANIA | 1995 | 460673507.078749 ROMANIA | 1994 | 462302634.832909 ROMANIA | 1993 | 453957891.190989 ROMANIA | 1992 | 463288308.809939 RUSSIA | 1998 | 282814539.282486 RUSSIA | 1997 | 473259028.282241 RUSSIA | 1996 | 478066207.921512 RUSSIA | 1995 | 474560946.100751 RUSSIA | 1994 | 470687815.99201 RUSSIA | 1993 | 469277788.189291 RUSSIA | 1992 | 481235318.39351 SAUDI ARABIA | 1998 | 276782996.256423 SAUDI ARABIA | 1997 | 469841492.269349 SAUDI ARABIA | 1996 | 475623237.142135 SAUDI ARABIA | 1995 | 481253780.946572 SAUDI ARABIA | 1994 | 473224839.866895 SAUDI ARABIA | 1993 | 471780149.478136 SAUDI ARABIA | 1992 | 469754732.67353 UNITED KINGDOM | 1998 | 277352051.155592 UNITED KINGDOM | 1997 | 464807797.013868 UNITED KINGDOM | 1996 | 462633807.246895 UNITED KINGDOM | 1995 | 466059954.973701 UNITED KINGDOM | 1994 | 461658299.4139 UNITED KINGDOM | 1993 | 464125471.752101 UNITED KINGDOM | 1992 | 462644479.76706 UNITED STATES | 1998 | 280049174.036128 UNITED STATES | 1997 | 471456730.781058 UNITED STATES | 1996 | 485710276.554469 UNITED STATES | 1995 | 481978825.737558 UNITED STATES | 1994 | 481768842.641109 UNITED STATES | 1993 | 473163004.838953 UNITED STATES | 1992 | 478169323.522509 VIETNAM | 1998 | 269182841.997498 VIETNAM | 1997 | 455442265.106533 VIETNAM | 1996 | 461079361.449824 VIETNAM | 1995 | 461774329.216018 VIETNAM | 1994 | 459712084.945419 VIETNAM | 1993 | 465659569.355743 VIETNAM | 1992 | 456956720.940468 (175 rows) COMMIT; COMMIT