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 '%sienna%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270489231.254624 ALGERIA | 1997 | 452322492.830357 ALGERIA | 1996 | 465088158.797572 ALGERIA | 1995 | 459731222.222912 ALGERIA | 1994 | 460061611.456061 ALGERIA | 1993 | 456069213.323299 ALGERIA | 1992 | 465162396.644532 ARGENTINA | 1998 | 279168378.740718 ARGENTINA | 1997 | 474968407.700262 ARGENTINA | 1996 | 470757534.059029 ARGENTINA | 1995 | 469399977.454846 ARGENTINA | 1994 | 470618539.240071 ARGENTINA | 1993 | 469441900.841501 ARGENTINA | 1992 | 478640016.867298 BRAZIL | 1998 | 279034323.495709 BRAZIL | 1997 | 473648567.368268 BRAZIL | 1996 | 474607022.885056 BRAZIL | 1995 | 467565717.640778 BRAZIL | 1994 | 467099053.858321 BRAZIL | 1993 | 468070150.353494 BRAZIL | 1992 | 484411569.069278 CANADA | 1998 | 278776808.640305 CANADA | 1997 | 476277932.177894 CANADA | 1996 | 479700733.449263 CANADA | 1995 | 473019005.414048 CANADA | 1994 | 481187808.6757 CANADA | 1993 | 473864084.311552 CANADA | 1992 | 479033342.195032 CHINA | 1998 | 275260995.534322 CHINA | 1997 | 469428296.248201 CHINA | 1996 | 467448919.392853 CHINA | 1995 | 475323071.954701 CHINA | 1994 | 470923131.994888 CHINA | 1993 | 471791156.322674 CHINA | 1992 | 468871675.139036 EGYPT | 1998 | 269097742.693189 EGYPT | 1997 | 465005190.445586 EGYPT | 1996 | 464598897.264093 EGYPT | 1995 | 458883152.573412 EGYPT | 1994 | 458896654.629838 EGYPT | 1993 | 463487096.367596 EGYPT | 1992 | 468700401.794796 ETHIOPIA | 1998 | 271933324.968608 ETHIOPIA | 1997 | 456973693.985917 ETHIOPIA | 1996 | 461801801.025398 ETHIOPIA | 1995 | 471280046.373528 ETHIOPIA | 1994 | 455269128.642445 ETHIOPIA | 1993 | 468507841.086167 ETHIOPIA | 1992 | 461845540.30761 FRANCE | 1998 | 273283601.345041 FRANCE | 1997 | 464155124.647148 FRANCE | 1996 | 472428919.664462 FRANCE | 1995 | 465728173.166572 FRANCE | 1994 | 462383927.644758 FRANCE | 1993 | 464759752.563969 FRANCE | 1992 | 471937876.314979 GERMANY | 1998 | 276315740.996809 GERMANY | 1997 | 473725313.653523 GERMANY | 1996 | 485024299.859464 GERMANY | 1995 | 477745832.448242 GERMANY | 1994 | 476194606.010007 GERMANY | 1993 | 474017373.581277 GERMANY | 1992 | 481101872.922904 INDIA | 1998 | 271721112.91434 INDIA | 1997 | 475852150.284021 INDIA | 1996 | 465897954.617125 INDIA | 1995 | 478430745.92974 INDIA | 1994 | 477122111.14854 INDIA | 1993 | 471454459.946319 INDIA | 1992 | 482808160.658651 INDONESIA | 1998 | 273195895.294194 INDONESIA | 1997 | 465448811.973765 INDONESIA | 1996 | 469374509.294303 INDONESIA | 1995 | 458864753.242781 INDONESIA | 1994 | 465156577.119182 INDONESIA | 1993 | 463831735.87711 INDONESIA | 1992 | 459316703.068392 IRAN | 1998 | 267566174.203265 IRAN | 1997 | 466910155.625839 IRAN | 1996 | 476459949.029477 IRAN | 1995 | 463745497.790448 IRAN | 1994 | 461209313.205334 IRAN | 1993 | 461489926.678834 IRAN | 1992 | 464074351.343509 IRAQ | 1998 | 275807398.076403 IRAQ | 1997 | 471327685.282913 IRAQ | 1996 | 472318488.704794 IRAQ | 1995 | 471916440.459931 IRAQ | 1994 | 474091021.324988 IRAQ | 1993 | 463196231.867544 IRAQ | 1992 | 471620988.866852 JAPAN | 1998 | 274384942.911298 JAPAN | 1997 | 473487112.074357 JAPAN | 1996 | 477055118.560648 JAPAN | 1995 | 462994548.625712 JAPAN | 1994 | 469110222.429276 JAPAN | 1993 | 465484732.373287 JAPAN | 1992 | 473472700.145529 JORDAN | 1998 | 269967124.829277 JORDAN | 1997 | 464369667.073883 JORDAN | 1996 | 463123638.057151 JORDAN | 1995 | 467180868.299024 JORDAN | 1994 | 464468763.362965 JORDAN | 1993 | 466728329.713887 JORDAN | 1992 | 461474061.14111 KENYA | 1998 | 275741941.964743 KENYA | 1997 | 466142104.392527 KENYA | 1996 | 476663728.844726 KENYA | 1995 | 467198078.467022 KENYA | 1994 | 472484615.911659 KENYA | 1993 | 476945108.759751 KENYA | 1992 | 471426539.001666 MOROCCO | 1998 | 274301978.316434 MOROCCO | 1997 | 475760427.684992 MOROCCO | 1996 | 470866358.558406 MOROCCO | 1995 | 467230124.127385 MOROCCO | 1994 | 470816359.987759 MOROCCO | 1993 | 472778556.180218 MOROCCO | 1992 | 466464531.12674 MOZAMBIQUE | 1998 | 270637358.658133 MOZAMBIQUE | 1997 | 461666051.261194 MOZAMBIQUE | 1996 | 460604307.759195 MOZAMBIQUE | 1995 | 460200497.571177 MOZAMBIQUE | 1994 | 461709397.269464 MOZAMBIQUE | 1993 | 452261454.649085 MOZAMBIQUE | 1992 | 455156247.482364 PERU | 1998 | 273641742.731986 PERU | 1997 | 467699974.319136 PERU | 1996 | 469938944.507163 PERU | 1995 | 469260116.894992 PERU | 1994 | 470446294.021677 PERU | 1993 | 466409765.621445 PERU | 1992 | 476680481.776095 ROMANIA | 1998 | 273831371.365117 ROMANIA | 1997 | 470736433.198127 ROMANIA | 1996 | 468705468.308614 ROMANIA | 1995 | 467580755.569071 ROMANIA | 1994 | 472449736.519479 ROMANIA | 1993 | 473232633.297526 ROMANIA | 1992 | 468629644.428999 RUSSIA | 1998 | 273912538.918884 RUSSIA | 1997 | 465098025.780777 RUSSIA | 1996 | 473298462.773225 RUSSIA | 1995 | 463679894.746108 RUSSIA | 1994 | 474062017.074676 RUSSIA | 1993 | 462627199.33561 RUSSIA | 1992 | 472548243.622198 SAUDI ARABIA | 1998 | 279042670.499675 SAUDI ARABIA | 1997 | 463328888.938664 SAUDI ARABIA | 1996 | 474045660.232389 SAUDI ARABIA | 1995 | 469796893.523218 SAUDI ARABIA | 1994 | 465915073.387455 SAUDI ARABIA | 1993 | 470399005.049466 SAUDI ARABIA | 1992 | 471793869.441493 UNITED KINGDOM | 1998 | 268626542.614852 UNITED KINGDOM | 1997 | 463783343.381687 UNITED KINGDOM | 1996 | 464855457.398979 UNITED KINGDOM | 1995 | 458533982.589977 UNITED KINGDOM | 1994 | 465100246.159262 UNITED KINGDOM | 1993 | 464965877.77723 UNITED KINGDOM | 1992 | 462153884.045621 UNITED STATES | 1998 | 276160845.171964 UNITED STATES | 1997 | 480791756.990176 UNITED STATES | 1996 | 489808183.197176 UNITED STATES | 1995 | 475414677.451357 UNITED STATES | 1994 | 475098862.491869 UNITED STATES | 1993 | 487361696.357901 UNITED STATES | 1992 | 478977562.891733 VIETNAM | 1998 | 282124518.125572 VIETNAM | 1997 | 461534767.203909 VIETNAM | 1996 | 475761129.37243 VIETNAM | 1995 | 465925135.773629 VIETNAM | 1994 | 464990795.525893 VIETNAM | 1993 | 465999370.735351 VIETNAM | 1992 | 470509001.752761 (175 rows) COMMIT; COMMIT