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 '%medium%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265783750.505182 ALGERIA | 1997 | 463378673.604657 ALGERIA | 1996 | 459444777.990597 ALGERIA | 1995 | 466501372.537065 ALGERIA | 1994 | 461709931.674106 ALGERIA | 1993 | 468099793.744437 ALGERIA | 1992 | 466616347.052597 ARGENTINA | 1998 | 275264035.009489 ARGENTINA | 1997 | 465835750.469022 ARGENTINA | 1996 | 472144179.362573 ARGENTINA | 1995 | 469205375.920631 ARGENTINA | 1994 | 469814569.177192 ARGENTINA | 1993 | 473482554.180188 ARGENTINA | 1992 | 470404367.515246 BRAZIL | 1998 | 272662347.874922 BRAZIL | 1997 | 462931145.520199 BRAZIL | 1996 | 475916016.278349 BRAZIL | 1995 | 468269711.373202 BRAZIL | 1994 | 461107219.273257 BRAZIL | 1993 | 463034861.406761 BRAZIL | 1992 | 471123908.633069 CANADA | 1998 | 273765708.992461 CANADA | 1997 | 476657060.386243 CANADA | 1996 | 476914619.090606 CANADA | 1995 | 476358203.642606 CANADA | 1994 | 476950797.60398 CANADA | 1993 | 474253286.760407 CANADA | 1992 | 479337332.884157 CHINA | 1998 | 269361063.760209 CHINA | 1997 | 462852044.587149 CHINA | 1996 | 466049968.697382 CHINA | 1995 | 463998598.451985 CHINA | 1994 | 466082157.993679 CHINA | 1993 | 467051400.913475 CHINA | 1992 | 470085537.218015 EGYPT | 1998 | 270218517.164603 EGYPT | 1997 | 465242006.084223 EGYPT | 1996 | 464952300.531507 EGYPT | 1995 | 458104678.490871 EGYPT | 1994 | 458304033.682048 EGYPT | 1993 | 469570902.045233 EGYPT | 1992 | 463184469.979505 ETHIOPIA | 1998 | 264854201.258369 ETHIOPIA | 1997 | 463677541.967195 ETHIOPIA | 1996 | 465415894.857434 ETHIOPIA | 1995 | 465692260.657522 ETHIOPIA | 1994 | 462352356.474533 ETHIOPIA | 1993 | 458627702.79471 ETHIOPIA | 1992 | 468910216.545592 FRANCE | 1998 | 263397821.869294 FRANCE | 1997 | 439521264.652791 FRANCE | 1996 | 457720803.358003 FRANCE | 1995 | 449263248.755498 FRANCE | 1994 | 453439352.689501 FRANCE | 1993 | 459123197.324839 FRANCE | 1992 | 448588882.488933 GERMANY | 1998 | 277975042.955055 GERMANY | 1997 | 477514053.751667 GERMANY | 1996 | 473716710.713958 GERMANY | 1995 | 475320148.688575 GERMANY | 1994 | 473877523.388664 GERMANY | 1993 | 482758186.631727 GERMANY | 1992 | 473737166.150486 INDIA | 1998 | 284577866.584428 INDIA | 1997 | 482789383.446338 INDIA | 1996 | 485541264.156433 INDIA | 1995 | 481943804.431626 INDIA | 1994 | 490496357.854408 INDIA | 1993 | 480826435.6211 INDIA | 1992 | 485415620.882875 INDONESIA | 1998 | 272379506.743901 INDONESIA | 1997 | 463223242.124817 INDONESIA | 1996 | 471406081.414319 INDONESIA | 1995 | 466174367.667001 INDONESIA | 1994 | 460358571.41141 INDONESIA | 1993 | 473319220.643216 INDONESIA | 1992 | 463667435.716021 IRAN | 1998 | 270817506.650263 IRAN | 1997 | 456524940.933649 IRAN | 1996 | 454244472.94626 IRAN | 1995 | 457601905.273016 IRAN | 1994 | 456768922.886283 IRAN | 1993 | 460504790.91197 IRAN | 1992 | 458132456.690812 IRAQ | 1998 | 276969007.904004 IRAQ | 1997 | 474212471.130027 IRAQ | 1996 | 472711099.58774 IRAQ | 1995 | 474541257.310905 IRAQ | 1994 | 466387549.757844 IRAQ | 1993 | 473250240.932542 IRAQ | 1992 | 467255250.123039 JAPAN | 1998 | 274559116.94472 JAPAN | 1997 | 461586318.363038 JAPAN | 1996 | 471741811.103812 JAPAN | 1995 | 460659092.643685 JAPAN | 1994 | 476087697.672025 JAPAN | 1993 | 466720408.733498 JAPAN | 1992 | 468269541.19804 JORDAN | 1998 | 269879035.050838 JORDAN | 1997 | 460208115.397914 JORDAN | 1996 | 460830937.907283 JORDAN | 1995 | 459727897.727198 JORDAN | 1994 | 457663703.809936 JORDAN | 1993 | 465410317.707489 JORDAN | 1992 | 465175645.830667 KENYA | 1998 | 275798390.274686 KENYA | 1997 | 471134033.814428 KENYA | 1996 | 473428998.833034 KENYA | 1995 | 473849438.976165 KENYA | 1994 | 463558214.418862 KENYA | 1993 | 467839221.785145 KENYA | 1992 | 470493728.696852 MOROCCO | 1998 | 275446624.644308 MOROCCO | 1997 | 466497433.958582 MOROCCO | 1996 | 466931811.089804 MOROCCO | 1995 | 476510939.944379 MOROCCO | 1994 | 470485563.099352 MOROCCO | 1993 | 473815546.596201 MOROCCO | 1992 | 468708301.306033 MOZAMBIQUE | 1998 | 271608134.128326 MOZAMBIQUE | 1997 | 464781884.791228 MOZAMBIQUE | 1996 | 466802283.809319 MOZAMBIQUE | 1995 | 465698966.340348 MOZAMBIQUE | 1994 | 456148352.603395 MOZAMBIQUE | 1993 | 459764586.37117 MOZAMBIQUE | 1992 | 455770078.478415 PERU | 1998 | 274636895.623399 PERU | 1997 | 468308103.722256 PERU | 1996 | 464174448.270634 PERU | 1995 | 464297085.246332 PERU | 1994 | 463437775.679974 PERU | 1993 | 459952715.949771 PERU | 1992 | 469270087.974281 ROMANIA | 1998 | 267141647.657709 ROMANIA | 1997 | 461667464.132722 ROMANIA | 1996 | 467540629.785523 ROMANIA | 1995 | 465118646.457561 ROMANIA | 1994 | 467945431.779972 ROMANIA | 1993 | 461987246.489886 ROMANIA | 1992 | 467986452.652566 RUSSIA | 1998 | 269033693.956615 RUSSIA | 1997 | 467703081.312772 RUSSIA | 1996 | 469243811.748708 RUSSIA | 1995 | 470301058.434718 RUSSIA | 1994 | 469277599.731809 RUSSIA | 1993 | 460794483.910445 RUSSIA | 1992 | 467601872.29515 SAUDI ARABIA | 1998 | 272743290.74041 SAUDI ARABIA | 1997 | 465389074.630856 SAUDI ARABIA | 1996 | 464824292.617138 SAUDI ARABIA | 1995 | 471145702.548403 SAUDI ARABIA | 1994 | 460738054.557439 SAUDI ARABIA | 1993 | 469490390.826318 SAUDI ARABIA | 1992 | 466731828.467092 UNITED KINGDOM | 1998 | 266563352.099089 UNITED KINGDOM | 1997 | 469793744.678946 UNITED KINGDOM | 1996 | 464751060.65801 UNITED KINGDOM | 1995 | 454352221.061975 UNITED KINGDOM | 1994 | 458306094.31585 UNITED KINGDOM | 1993 | 466669597.765075 UNITED KINGDOM | 1992 | 458206444.937234 UNITED STATES | 1998 | 279606035.57107 UNITED STATES | 1997 | 476002043.1878 UNITED STATES | 1996 | 479356078.728589 UNITED STATES | 1995 | 470613795.663226 UNITED STATES | 1994 | 477423773.778951 UNITED STATES | 1993 | 479715748.931177 UNITED STATES | 1992 | 467513578.614988 VIETNAM | 1998 | 269146820.373385 VIETNAM | 1997 | 463021437.021212 VIETNAM | 1996 | 474300621.981327 VIETNAM | 1995 | 463242466.786718 VIETNAM | 1994 | 470598170.155545 VIETNAM | 1993 | 468012794.464362 VIETNAM | 1992 | 474749410.475129 (175 rows) COMMIT; COMMIT