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 '%metallic%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268876114.196097 ALGERIA | 1997 | 462107155.158111 ALGERIA | 1996 | 455583973.562974 ALGERIA | 1995 | 462983498.470054 ALGERIA | 1994 | 460074922.956667 ALGERIA | 1993 | 460340414.560871 ALGERIA | 1992 | 454065828.245223 ARGENTINA | 1998 | 275650944.972661 ARGENTINA | 1997 | 470108779.382903 ARGENTINA | 1996 | 464533836.818624 ARGENTINA | 1995 | 463833584.489251 ARGENTINA | 1994 | 467401108.372708 ARGENTINA | 1993 | 469244028.038708 ARGENTINA | 1992 | 457893748.13104 BRAZIL | 1998 | 270598384.657374 BRAZIL | 1997 | 456901432.904459 BRAZIL | 1996 | 462691703.495035 BRAZIL | 1995 | 455466813.104 BRAZIL | 1994 | 461156987.753196 BRAZIL | 1993 | 465415873.493155 BRAZIL | 1992 | 470120473.249042 CANADA | 1998 | 278042220.89312 CANADA | 1997 | 470924151.457207 CANADA | 1996 | 477067839.793272 CANADA | 1995 | 474352179.487268 CANADA | 1994 | 478857845.013267 CANADA | 1993 | 464769343.130847 CANADA | 1992 | 468239920.964897 CHINA | 1998 | 270389190.652335 CHINA | 1997 | 465020889.47418 CHINA | 1996 | 465322699.706567 CHINA | 1995 | 456262723.476929 CHINA | 1994 | 453178247.323894 CHINA | 1993 | 458699296.929653 CHINA | 1992 | 464739431.019092 EGYPT | 1998 | 278193647.269085 EGYPT | 1997 | 473273180.160286 EGYPT | 1996 | 471882870.784987 EGYPT | 1995 | 472568098.173814 EGYPT | 1994 | 478655246.775768 EGYPT | 1993 | 469648835.97103 EGYPT | 1992 | 474733608.327947 ETHIOPIA | 1998 | 262394357.725337 ETHIOPIA | 1997 | 464311712.864839 ETHIOPIA | 1996 | 456883748.280263 ETHIOPIA | 1995 | 447551706.093492 ETHIOPIA | 1994 | 453516134.031905 ETHIOPIA | 1993 | 455123997.912834 ETHIOPIA | 1992 | 455197143.289189 FRANCE | 1998 | 268169415.039812 FRANCE | 1997 | 464493073.208716 FRANCE | 1996 | 471129445.298808 FRANCE | 1995 | 462104675.383938 FRANCE | 1994 | 465719846.897083 FRANCE | 1993 | 460700498.871126 FRANCE | 1992 | 467383345.444035 GERMANY | 1998 | 277677036.750665 GERMANY | 1997 | 473072340.755395 GERMANY | 1996 | 475550715.97366 GERMANY | 1995 | 473762554.229072 GERMANY | 1994 | 473500453.409181 GERMANY | 1993 | 473424125.999682 GERMANY | 1992 | 473446043.028948 INDIA | 1998 | 274494410.395216 INDIA | 1997 | 472047949.181359 INDIA | 1996 | 471665987.599818 INDIA | 1995 | 458985483.567697 INDIA | 1994 | 464716056.898555 INDIA | 1993 | 474763702.247176 INDIA | 1992 | 474399410.543097 INDONESIA | 1998 | 267007425.754785 INDONESIA | 1997 | 456988773.272589 INDONESIA | 1996 | 459337707.541689 INDONESIA | 1995 | 451461442.288702 INDONESIA | 1994 | 464850359.764131 INDONESIA | 1993 | 455791683.183372 INDONESIA | 1992 | 457494963.467727 IRAN | 1998 | 272061061.731124 IRAN | 1997 | 465998449.223891 IRAN | 1996 | 470692830.207576 IRAN | 1995 | 466272136.808996 IRAN | 1994 | 465520720.387504 IRAN | 1993 | 464485624.401554 IRAN | 1992 | 463910482.145154 IRAQ | 1998 | 271528578.190822 IRAQ | 1997 | 466625133.670585 IRAQ | 1996 | 469413186.784927 IRAQ | 1995 | 471326958.676844 IRAQ | 1994 | 471549182.730824 IRAQ | 1993 | 471823126.530036 IRAQ | 1992 | 473417018.718257 JAPAN | 1998 | 271541454.840752 JAPAN | 1997 | 464857841.431196 JAPAN | 1996 | 463795323.227878 JAPAN | 1995 | 464098138.39871 JAPAN | 1994 | 458794545.614281 JAPAN | 1993 | 466004963.031495 JAPAN | 1992 | 461491812.042701 JORDAN | 1998 | 269885340.327489 JORDAN | 1997 | 455092449.310311 JORDAN | 1996 | 455946922.195181 JORDAN | 1995 | 464876424.980159 JORDAN | 1994 | 457706257.787836 JORDAN | 1993 | 455813258.88471 JORDAN | 1992 | 457285017.929084 KENYA | 1998 | 274411120.775014 KENYA | 1997 | 468991638.58578 KENYA | 1996 | 466409799.595528 KENYA | 1995 | 468716590.880065 KENYA | 1994 | 466924333.949444 KENYA | 1993 | 468794301.266625 KENYA | 1992 | 472888046.196551 MOROCCO | 1998 | 274027509.707822 MOROCCO | 1997 | 464296364.622617 MOROCCO | 1996 | 461505096.268332 MOROCCO | 1995 | 461736729.543076 MOROCCO | 1994 | 465613120.696183 MOROCCO | 1993 | 461111049.251224 MOROCCO | 1992 | 459305836.667821 MOZAMBIQUE | 1998 | 271072325.195792 MOZAMBIQUE | 1997 | 467144983.997406 MOZAMBIQUE | 1996 | 468419388.06002 MOZAMBIQUE | 1995 | 471205527.694609 MOZAMBIQUE | 1994 | 463032194.50841 MOZAMBIQUE | 1993 | 463834809.437261 MOZAMBIQUE | 1992 | 465064335.990537 PERU | 1998 | 263868921.825116 PERU | 1997 | 458383571.276455 PERU | 1996 | 469681774.98793 PERU | 1995 | 462022038.09973 PERU | 1994 | 452889219.381135 PERU | 1993 | 458985621.860053 PERU | 1992 | 459105057.508377 ROMANIA | 1998 | 279971045.833045 ROMANIA | 1997 | 468983634.702159 ROMANIA | 1996 | 467189430.756663 ROMANIA | 1995 | 461277454.458046 ROMANIA | 1994 | 475837295.63939 ROMANIA | 1993 | 468259512.31017 ROMANIA | 1992 | 473821319.932274 RUSSIA | 1998 | 280563993.366314 RUSSIA | 1997 | 472759045.138787 RUSSIA | 1996 | 475751992.712336 RUSSIA | 1995 | 468277318.960525 RUSSIA | 1994 | 479857400.737049 RUSSIA | 1993 | 469170272.95705 RUSSIA | 1992 | 468213910.170283 SAUDI ARABIA | 1998 | 274982155.166909 SAUDI ARABIA | 1997 | 468622483.026457 SAUDI ARABIA | 1996 | 471782181.573569 SAUDI ARABIA | 1995 | 469153637.626917 SAUDI ARABIA | 1994 | 460632887.661447 SAUDI ARABIA | 1993 | 466632857.125544 SAUDI ARABIA | 1992 | 465549838.157436 UNITED KINGDOM | 1998 | 271470070.342603 UNITED KINGDOM | 1997 | 461422935.864317 UNITED KINGDOM | 1996 | 465989337.765835 UNITED KINGDOM | 1995 | 463413269.083588 UNITED KINGDOM | 1994 | 459014225.413805 UNITED KINGDOM | 1993 | 462700448.748983 UNITED KINGDOM | 1992 | 457377854.607866 UNITED STATES | 1998 | 266106638.553435 UNITED STATES | 1997 | 465034758.704923 UNITED STATES | 1996 | 465524561.394004 UNITED STATES | 1995 | 467412250.145834 UNITED STATES | 1994 | 471550747.355075 UNITED STATES | 1993 | 465927990.515787 UNITED STATES | 1992 | 461716191.515751 VIETNAM | 1998 | 269493567.551074 VIETNAM | 1997 | 458155654.130667 VIETNAM | 1996 | 466463472.361818 VIETNAM | 1995 | 457953536.362277 VIETNAM | 1994 | 468781692.375482 VIETNAM | 1993 | 469404512.89183 VIETNAM | 1992 | 461968036.862374 (175 rows) COMMIT; COMMIT