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 | 261343491.866299 ALGERIA | 1997 | 452887087.70173 ALGERIA | 1996 | 457409892.626083 ALGERIA | 1995 | 458098809.851471 ALGERIA | 1994 | 454121559.506084 ALGERIA | 1993 | 448820506.969161 ALGERIA | 1992 | 454505557.509509 ARGENTINA | 1998 | 281928560.879703 ARGENTINA | 1997 | 477139624.560068 ARGENTINA | 1996 | 483933631.016678 ARGENTINA | 1995 | 472499379.198442 ARGENTINA | 1994 | 471632539.195913 ARGENTINA | 1993 | 475914279.1546 ARGENTINA | 1992 | 474770988.150249 BRAZIL | 1998 | 272336506.841177 BRAZIL | 1997 | 476606117.851806 BRAZIL | 1996 | 472630597.052374 BRAZIL | 1995 | 474043669.476803 BRAZIL | 1994 | 469975901.525588 BRAZIL | 1993 | 465210877.179561 BRAZIL | 1992 | 476484813.042736 CANADA | 1998 | 277833938.387154 CANADA | 1997 | 475411006.719718 CANADA | 1996 | 482288588.078064 CANADA | 1995 | 477997552.815791 CANADA | 1994 | 483862304.158114 CANADA | 1993 | 483513083.781267 CANADA | 1992 | 474744154.667803 CHINA | 1998 | 273215729.761749 CHINA | 1997 | 460449844.959476 CHINA | 1996 | 459011685.845285 CHINA | 1995 | 464045293.152627 CHINA | 1994 | 464643771.561222 CHINA | 1993 | 462031203.7897 CHINA | 1992 | 467487782.786252 EGYPT | 1998 | 270522547.493047 EGYPT | 1997 | 467000565.138282 EGYPT | 1996 | 470855492.703761 EGYPT | 1995 | 471562113.405111 EGYPT | 1994 | 466633709.734815 EGYPT | 1993 | 462725228.104096 EGYPT | 1992 | 468127894.062353 ETHIOPIA | 1998 | 271947182.178104 ETHIOPIA | 1997 | 452102700.406633 ETHIOPIA | 1996 | 455153793.467513 ETHIOPIA | 1995 | 456526224.583363 ETHIOPIA | 1994 | 454663817.948953 ETHIOPIA | 1993 | 456452204.047426 ETHIOPIA | 1992 | 453828169.039129 FRANCE | 1998 | 268401358.086788 FRANCE | 1997 | 454238618.520448 FRANCE | 1996 | 462525717.032691 FRANCE | 1995 | 468850749.125182 FRANCE | 1994 | 461982512.143582 FRANCE | 1993 | 461112888.830404 FRANCE | 1992 | 456434861.680131 GERMANY | 1998 | 279909178.852512 GERMANY | 1997 | 478735655.878256 GERMANY | 1996 | 485505584.831465 GERMANY | 1995 | 490795221.400689 GERMANY | 1994 | 477554411.041575 GERMANY | 1993 | 483499844.154733 GERMANY | 1992 | 481862992.447607 INDIA | 1998 | 277684719.972184 INDIA | 1997 | 472562598.384757 INDIA | 1996 | 472936287.993781 INDIA | 1995 | 480107552.251804 INDIA | 1994 | 482666059.780085 INDIA | 1993 | 475359390.916018 INDIA | 1992 | 474487356.491268 INDONESIA | 1998 | 274544677.184623 INDONESIA | 1997 | 464495616.491905 INDONESIA | 1996 | 463229670.55961 INDONESIA | 1995 | 472008015.610928 INDONESIA | 1994 | 462152518.299502 INDONESIA | 1993 | 463072765.368103 INDONESIA | 1992 | 460910155.054509 IRAN | 1998 | 268811561.183141 IRAN | 1997 | 464386413.270535 IRAN | 1996 | 461098951.358748 IRAN | 1995 | 455036010.505139 IRAN | 1994 | 457448322.608985 IRAN | 1993 | 456159046.072982 IRAN | 1992 | 463962114.808023 IRAQ | 1998 | 281808851.316454 IRAQ | 1997 | 476973350.260735 IRAQ | 1996 | 474784778.494525 IRAQ | 1995 | 482673467.929115 IRAQ | 1994 | 479383247.777105 IRAQ | 1993 | 481779117.926215 IRAQ | 1992 | 482300932.706669 JAPAN | 1998 | 277228852.274874 JAPAN | 1997 | 467135524.910145 JAPAN | 1996 | 466659476.203994 JAPAN | 1995 | 472512217.191596 JAPAN | 1994 | 474905589.414773 JAPAN | 1993 | 474306971.277663 JAPAN | 1992 | 473899892.328647 JORDAN | 1998 | 273382689.961362 JORDAN | 1997 | 458350064.639971 JORDAN | 1996 | 469894512.555475 JORDAN | 1995 | 465638349.943645 JORDAN | 1994 | 456920252.796119 JORDAN | 1993 | 465908537.481739 JORDAN | 1992 | 463045239.973602 KENYA | 1998 | 274489319.506109 KENYA | 1997 | 480071476.137339 KENYA | 1996 | 474449765.504716 KENYA | 1995 | 479196730.300269 KENYA | 1994 | 475396616.090473 KENYA | 1993 | 477301635.583746 KENYA | 1992 | 476573425.345807 MOROCCO | 1998 | 276828342.958528 MOROCCO | 1997 | 482106822.177048 MOROCCO | 1996 | 474299846.795466 MOROCCO | 1995 | 480868939.331935 MOROCCO | 1994 | 471242346.023344 MOROCCO | 1993 | 478946435.465638 MOROCCO | 1992 | 477555971.851095 MOZAMBIQUE | 1998 | 272694144.594648 MOZAMBIQUE | 1997 | 458430789.154642 MOZAMBIQUE | 1996 | 461715305.611751 MOZAMBIQUE | 1995 | 461555150.896761 MOZAMBIQUE | 1994 | 466515856.767101 MOZAMBIQUE | 1993 | 457762395.313547 MOZAMBIQUE | 1992 | 459576092.947255 PERU | 1998 | 281427274.673651 PERU | 1997 | 462787805.536449 PERU | 1996 | 461882972.265697 PERU | 1995 | 465410839.517578 PERU | 1994 | 458158554.08485 PERU | 1993 | 462860472.991286 PERU | 1992 | 466665793.774611 ROMANIA | 1998 | 271040407.000831 ROMANIA | 1997 | 476360199.356209 ROMANIA | 1996 | 471354014.741855 ROMANIA | 1995 | 471356905.777244 ROMANIA | 1994 | 472462329.251518 ROMANIA | 1993 | 467157989.747492 ROMANIA | 1992 | 469536276.385247 RUSSIA | 1998 | 284656832.513499 RUSSIA | 1997 | 478336465.537606 RUSSIA | 1996 | 482354058.203311 RUSSIA | 1995 | 471472758.969738 RUSSIA | 1994 | 478285309.281305 RUSSIA | 1993 | 481008700.146402 RUSSIA | 1992 | 475373956.909979 SAUDI ARABIA | 1998 | 272523605.026569 SAUDI ARABIA | 1997 | 464809448.738452 SAUDI ARABIA | 1996 | 472755610.827307 SAUDI ARABIA | 1995 | 468266818.870998 SAUDI ARABIA | 1994 | 465841206.529332 SAUDI ARABIA | 1993 | 453209404.057285 SAUDI ARABIA | 1992 | 470568237.467089 UNITED KINGDOM | 1998 | 270379754.843139 UNITED KINGDOM | 1997 | 462156501.058673 UNITED KINGDOM | 1996 | 468544244.656361 UNITED KINGDOM | 1995 | 466301085.990358 UNITED KINGDOM | 1994 | 457794254.174625 UNITED KINGDOM | 1993 | 466153564.409296 UNITED KINGDOM | 1992 | 463352363.035426 UNITED STATES | 1998 | 272454605.169492 UNITED STATES | 1997 | 472330564.144011 UNITED STATES | 1996 | 468702414.75387 UNITED STATES | 1995 | 471305159.890085 UNITED STATES | 1994 | 475662272.499798 UNITED STATES | 1993 | 471087775.102137 UNITED STATES | 1992 | 464415127.463496 VIETNAM | 1998 | 267019027.576868 VIETNAM | 1997 | 460853473.938244 VIETNAM | 1996 | 454681565.215556 VIETNAM | 1995 | 457301450.916925 VIETNAM | 1994 | 453850266.226234 VIETNAM | 1993 | 461145131.634958 VIETNAM | 1992 | 460541667.071804 (175 rows) COMMIT; COMMIT