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 '%aquamarine%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262545735.126587 ALGERIA | 1997 | 470441290.143915 ALGERIA | 1996 | 467943217.35091 ALGERIA | 1995 | 463697537.691627 ALGERIA | 1994 | 455356463.646103 ALGERIA | 1993 | 455180730.920697 ALGERIA | 1992 | 462758817.268023 ARGENTINA | 1998 | 273345401.133264 ARGENTINA | 1997 | 468710043.233715 ARGENTINA | 1996 | 461226130.662247 ARGENTINA | 1995 | 467384972.795015 ARGENTINA | 1994 | 466673135.439755 ARGENTINA | 1993 | 462418064.75131 ARGENTINA | 1992 | 466532566.10452 BRAZIL | 1998 | 273457233.229096 BRAZIL | 1997 | 469901645.895715 BRAZIL | 1996 | 466194781.352613 BRAZIL | 1995 | 469448572.846287 BRAZIL | 1994 | 465269981.047172 BRAZIL | 1993 | 470559601.326912 BRAZIL | 1992 | 465755174.325958 CANADA | 1998 | 277595922.34965 CANADA | 1997 | 468418892.017942 CANADA | 1996 | 472750080.340078 CANADA | 1995 | 473630854.057382 CANADA | 1994 | 475546949.783597 CANADA | 1993 | 470120974.042837 CANADA | 1992 | 479039034.323864 CHINA | 1998 | 267120020.610706 CHINA | 1997 | 465987218.457619 CHINA | 1996 | 459754268.369939 CHINA | 1995 | 457606263.642506 CHINA | 1994 | 467139584.816909 CHINA | 1993 | 462029913.842096 CHINA | 1992 | 463288871.704997 EGYPT | 1998 | 270927784.109221 EGYPT | 1997 | 469309558.010436 EGYPT | 1996 | 468673891.529351 EGYPT | 1995 | 467041371.376348 EGYPT | 1994 | 462581180.322858 EGYPT | 1993 | 468151833.474097 EGYPT | 1992 | 469800421.03566 ETHIOPIA | 1998 | 268930144.961455 ETHIOPIA | 1997 | 456766466.366892 ETHIOPIA | 1996 | 453296915.525979 ETHIOPIA | 1995 | 456522277.40901 ETHIOPIA | 1994 | 459086141.363152 ETHIOPIA | 1993 | 460311897.761842 ETHIOPIA | 1992 | 452590155.07474 FRANCE | 1998 | 260591217.371602 FRANCE | 1997 | 448969047.636658 FRANCE | 1996 | 451741793.853804 FRANCE | 1995 | 447133160.083605 FRANCE | 1994 | 453907523.881127 FRANCE | 1993 | 449834542.26528 FRANCE | 1992 | 455449832.814983 GERMANY | 1998 | 270199399.338824 GERMANY | 1997 | 474073575.944565 GERMANY | 1996 | 472160672.84924 GERMANY | 1995 | 475754969.804114 GERMANY | 1994 | 470533410.71944 GERMANY | 1993 | 469677144.374098 GERMANY | 1992 | 468485479.731976 INDIA | 1998 | 279464150.792162 INDIA | 1997 | 473036823.662248 INDIA | 1996 | 472724340.526207 INDIA | 1995 | 472601012.412073 INDIA | 1994 | 475395792.277126 INDIA | 1993 | 479176480.476218 INDIA | 1992 | 479969336.414407 INDONESIA | 1998 | 264851129.664102 INDONESIA | 1997 | 458523505.378784 INDONESIA | 1996 | 460349007.032338 INDONESIA | 1995 | 457620693.228486 INDONESIA | 1994 | 461767967.911432 INDONESIA | 1993 | 458810862.043795 INDONESIA | 1992 | 460982481.73933 IRAN | 1998 | 269488374.921065 IRAN | 1997 | 460313063.142937 IRAN | 1996 | 462187523.557433 IRAN | 1995 | 462669664.24725 IRAN | 1994 | 466644345.772767 IRAN | 1993 | 466531723.651567 IRAN | 1992 | 468983392.649603 IRAQ | 1998 | 278739298.43908 IRAQ | 1997 | 476834910.266332 IRAQ | 1996 | 475120967.647551 IRAQ | 1995 | 472913485.662272 IRAQ | 1994 | 472855848.104032 IRAQ | 1993 | 468224657.888971 IRAQ | 1992 | 473817057.833394 JAPAN | 1998 | 266847436.878965 JAPAN | 1997 | 460852547.741936 JAPAN | 1996 | 464610382.855911 JAPAN | 1995 | 460962229.488316 JAPAN | 1994 | 459081196.401917 JAPAN | 1993 | 462224103.636822 JAPAN | 1992 | 462208888.187705 JORDAN | 1998 | 271512923.679864 JORDAN | 1997 | 465988234.541994 JORDAN | 1996 | 456139593.694279 JORDAN | 1995 | 463632940.363916 JORDAN | 1994 | 463683257.550611 JORDAN | 1993 | 466797982.106057 JORDAN | 1992 | 467212182.422394 KENYA | 1998 | 270350354.970782 KENYA | 1997 | 464681317.44281 KENYA | 1996 | 468905009.92024 KENYA | 1995 | 467149449.358288 KENYA | 1994 | 473895995.602203 KENYA | 1993 | 464872139.522691 KENYA | 1992 | 466152399.479089 MOROCCO | 1998 | 272151636.981129 MOROCCO | 1997 | 468036581.386455 MOROCCO | 1996 | 466440306.029138 MOROCCO | 1995 | 467393988.289368 MOROCCO | 1994 | 460984842.7897 MOROCCO | 1993 | 466267253.286048 MOROCCO | 1992 | 472332309.25103 MOZAMBIQUE | 1998 | 265546194.221392 MOZAMBIQUE | 1997 | 461100036.60271 MOZAMBIQUE | 1996 | 458466563.124315 MOZAMBIQUE | 1995 | 455589493.935977 MOZAMBIQUE | 1994 | 458026055.122047 MOZAMBIQUE | 1993 | 461792211.989447 MOZAMBIQUE | 1992 | 456290202.265424 PERU | 1998 | 271818899.776948 PERU | 1997 | 462521330.81499 PERU | 1996 | 470700242.486028 PERU | 1995 | 460917867.70495 PERU | 1994 | 464560083.915839 PERU | 1993 | 461429674.302425 PERU | 1992 | 455851135.295105 ROMANIA | 1998 | 280391035.597411 ROMANIA | 1997 | 482646697.595823 ROMANIA | 1996 | 480485846.509582 ROMANIA | 1995 | 481072002.810372 ROMANIA | 1994 | 474343794.16342 ROMANIA | 1993 | 476714148.002141 ROMANIA | 1992 | 485332412.215695 RUSSIA | 1998 | 275547286.251848 RUSSIA | 1997 | 469829821.688092 RUSSIA | 1996 | 476493434.643485 RUSSIA | 1995 | 465317391.051619 RUSSIA | 1994 | 466961396.09672 RUSSIA | 1993 | 469252424.872805 RUSSIA | 1992 | 462748759.035444 SAUDI ARABIA | 1998 | 277911267.596317 SAUDI ARABIA | 1997 | 461129648.819264 SAUDI ARABIA | 1996 | 476289272.887736 SAUDI ARABIA | 1995 | 456976242.160256 SAUDI ARABIA | 1994 | 466373929.380315 SAUDI ARABIA | 1993 | 464518970.519444 SAUDI ARABIA | 1992 | 466724804.448146 UNITED KINGDOM | 1998 | 273264037.587026 UNITED KINGDOM | 1997 | 473604870.80538 UNITED KINGDOM | 1996 | 466586670.490292 UNITED KINGDOM | 1995 | 467444187.344688 UNITED KINGDOM | 1994 | 463156218.450537 UNITED KINGDOM | 1993 | 462526235.693545 UNITED KINGDOM | 1992 | 462725625.534591 UNITED STATES | 1998 | 270917153.135552 UNITED STATES | 1997 | 465550731.136995 UNITED STATES | 1996 | 456381657.743494 UNITED STATES | 1995 | 459212947.918918 UNITED STATES | 1994 | 464431106.66748 UNITED STATES | 1993 | 467015349.498949 UNITED STATES | 1992 | 465618515.11411 VIETNAM | 1998 | 270917525.551995 VIETNAM | 1997 | 474303993.845298 VIETNAM | 1996 | 479981135.996304 VIETNAM | 1995 | 467488871.442643 VIETNAM | 1994 | 472122545.953037 VIETNAM | 1993 | 466743457.101015 VIETNAM | 1992 | 472985005.45195 (175 rows) COMMIT; COMMIT