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 '%pink%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261491674.451622 ALGERIA | 1997 | 453294224.789242 ALGERIA | 1996 | 457510096.566126 ALGERIA | 1995 | 457939379.596361 ALGERIA | 1994 | 454478729.580412 ALGERIA | 1993 | 448873701.431229 ALGERIA | 1992 | 454730381.867328 ARGENTINA | 1998 | 282113267.417014 ARGENTINA | 1997 | 476604221.320968 ARGENTINA | 1996 | 484108379.71805 ARGENTINA | 1995 | 472344459.270723 ARGENTINA | 1994 | 472046399.372884 ARGENTINA | 1993 | 476186432.472406 ARGENTINA | 1992 | 474755830.100168 BRAZIL | 1998 | 272413734.500835 BRAZIL | 1997 | 476319774.121819 BRAZIL | 1996 | 472359472.721708 BRAZIL | 1995 | 474172413.844114 BRAZIL | 1994 | 469676431.576675 BRAZIL | 1993 | 465351169.332961 BRAZIL | 1992 | 476441519.968774 CANADA | 1998 | 277892041.026002 CANADA | 1997 | 475318163.971786 CANADA | 1996 | 482183164.551336 CANADA | 1995 | 477679986.30093 CANADA | 1994 | 483689863.422898 CANADA | 1993 | 483750284.640028 CANADA | 1992 | 475146175.013756 CHINA | 1998 | 272999565.511365 CHINA | 1997 | 460310069.296161 CHINA | 1996 | 459025344.190048 CHINA | 1995 | 463902545.359265 CHINA | 1994 | 464511014.408064 CHINA | 1993 | 461604409.811699 CHINA | 1992 | 467387930.85943 EGYPT | 1998 | 270632406.756992 EGYPT | 1997 | 466954855.693078 EGYPT | 1996 | 470710355.92527 EGYPT | 1995 | 471295557.515486 EGYPT | 1994 | 466890463.558347 EGYPT | 1993 | 462538497.390466 EGYPT | 1992 | 468155938.008728 ETHIOPIA | 1998 | 272011661.277848 ETHIOPIA | 1997 | 451968025.065383 ETHIOPIA | 1996 | 455008880.80286 ETHIOPIA | 1995 | 456419583.795996 ETHIOPIA | 1994 | 455078759.266356 ETHIOPIA | 1993 | 456579677.443155 ETHIOPIA | 1992 | 453905160.831163 FRANCE | 1998 | 268573964.212675 FRANCE | 1997 | 454675320.932408 FRANCE | 1996 | 462651179.433317 FRANCE | 1995 | 469004938.727415 FRANCE | 1994 | 461969444.653664 FRANCE | 1993 | 460703140.101755 FRANCE | 1992 | 456544431.631761 GERMANY | 1998 | 279856389.810694 GERMANY | 1997 | 478742205.043665 GERMANY | 1996 | 485377433.921512 GERMANY | 1995 | 490748845.380015 GERMANY | 1994 | 477664770.436402 GERMANY | 1993 | 483596067.447884 GERMANY | 1992 | 481656532.036289 INDIA | 1998 | 277698319.750385 INDIA | 1997 | 472936190.004166 INDIA | 1996 | 473191881.211972 INDIA | 1995 | 479873324.720284 INDIA | 1994 | 482682496.407233 INDIA | 1993 | 475621667.614705 INDIA | 1992 | 474762334.600869 INDONESIA | 1998 | 274416235.422817 INDONESIA | 1997 | 464405284.852566 INDONESIA | 1996 | 463229764.500642 INDONESIA | 1995 | 472068721.802354 INDONESIA | 1994 | 462019243.184162 INDONESIA | 1993 | 463093624.869557 INDONESIA | 1992 | 460779202.77999 IRAN | 1998 | 268884125.334379 IRAN | 1997 | 464404555.745685 IRAN | 1996 | 461155783.583667 IRAN | 1995 | 455122055.348717 IRAN | 1994 | 457501841.655327 IRAN | 1993 | 455879284.917502 IRAN | 1992 | 464023137.252096 IRAQ | 1998 | 281883567.295207 IRAQ | 1997 | 477053306.117485 IRAQ | 1996 | 474744140.553397 IRAQ | 1995 | 482588441.374833 IRAQ | 1994 | 479660565.947104 IRAQ | 1993 | 482102428.151595 IRAQ | 1992 | 482394712.322485 JAPAN | 1998 | 277042321.071838 JAPAN | 1997 | 467219500.562208 JAPAN | 1996 | 467005956.824328 JAPAN | 1995 | 472505721.420826 JAPAN | 1994 | 475173815.309055 JAPAN | 1993 | 474280247.365108 JAPAN | 1992 | 474370405.229165 JORDAN | 1998 | 273334820.714911 JORDAN | 1997 | 458244993.696384 JORDAN | 1996 | 469458233.389941 JORDAN | 1995 | 465576543.278665 JORDAN | 1994 | 456711457.229728 JORDAN | 1993 | 465973832.462545 JORDAN | 1992 | 463293104.087295 KENYA | 1998 | 274107632.266385 KENYA | 1997 | 480135629.235692 KENYA | 1996 | 474487931.654159 KENYA | 1995 | 478584495.078491 KENYA | 1994 | 475757931.604983 KENYA | 1993 | 477405200.515717 KENYA | 1992 | 476610167.305853 MOROCCO | 1998 | 276493089.928116 MOROCCO | 1997 | 482255016.86607 MOROCCO | 1996 | 474445442.018485 MOROCCO | 1995 | 480841364.698273 MOROCCO | 1994 | 471064629.172486 MOROCCO | 1993 | 479410987.060129 MOROCCO | 1992 | 477662828.586117 MOZAMBIQUE | 1998 | 272705462.547015 MOZAMBIQUE | 1997 | 458628998.032968 MOZAMBIQUE | 1996 | 461810439.91892 MOZAMBIQUE | 1995 | 461742191.672388 MOZAMBIQUE | 1994 | 466147215.787389 MOZAMBIQUE | 1993 | 458034288.723393 MOZAMBIQUE | 1992 | 459382629.874901 PERU | 1998 | 281449973.891618 PERU | 1997 | 462793398.308931 PERU | 1996 | 461892893.585498 PERU | 1995 | 465539091.057709 PERU | 1994 | 458386650.329609 PERU | 1993 | 463081117.251218 PERU | 1992 | 466691282.390373 ROMANIA | 1998 | 271219782.997518 ROMANIA | 1997 | 476502057.567729 ROMANIA | 1996 | 471747812.588817 ROMANIA | 1995 | 471203362.587732 ROMANIA | 1994 | 472218765.699529 ROMANIA | 1993 | 467080553.482139 ROMANIA | 1992 | 469564874.021812 RUSSIA | 1998 | 284454151.298112 RUSSIA | 1997 | 478189194.439954 RUSSIA | 1996 | 482078281.39 RUSSIA | 1995 | 471310076.376168 RUSSIA | 1994 | 477637055.929622 RUSSIA | 1993 | 481242458.079965 RUSSIA | 1992 | 475203830.955517 SAUDI ARABIA | 1998 | 272534851.412506 SAUDI ARABIA | 1997 | 465076980.344421 SAUDI ARABIA | 1996 | 472418372.625249 SAUDI ARABIA | 1995 | 468540511.992344 SAUDI ARABIA | 1994 | 465808863.91812 SAUDI ARABIA | 1993 | 453535074.462205 SAUDI ARABIA | 1992 | 470926193.848008 UNITED KINGDOM | 1998 | 270317187.695945 UNITED KINGDOM | 1997 | 462316756.972734 UNITED KINGDOM | 1996 | 468252894.867595 UNITED KINGDOM | 1995 | 466558094.880316 UNITED KINGDOM | 1994 | 457711888.478078 UNITED KINGDOM | 1993 | 466084992.082094 UNITED KINGDOM | 1992 | 463849846.947913 UNITED STATES | 1998 | 272506190.375818 UNITED STATES | 1997 | 472296585.446696 UNITED STATES | 1996 | 469107114.469086 UNITED STATES | 1995 | 471247902.571901 UNITED STATES | 1994 | 475589745.379274 UNITED STATES | 1993 | 471137542.12334 UNITED STATES | 1992 | 464282116.945193 VIETNAM | 1998 | 266938815.435966 VIETNAM | 1997 | 460454994.019499 VIETNAM | 1996 | 454611920.949691 VIETNAM | 1995 | 457592107.857205 VIETNAM | 1994 | 453763789.179019 VIETNAM | 1993 | 461124929.809765 VIETNAM | 1992 | 460750412.256815 (175 rows) COMMIT; COMMIT