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 | 266297453.435315 ALGERIA | 1997 | 463003913.503498 ALGERIA | 1996 | 459810771.585022 ALGERIA | 1995 | 466383797.151036 ALGERIA | 1994 | 462440231.60504 ALGERIA | 1993 | 467681860.001185 ALGERIA | 1992 | 466823953.526653 ARGENTINA | 1998 | 275041667.50238 ARGENTINA | 1997 | 465462654.232564 ARGENTINA | 1996 | 472261430.041553 ARGENTINA | 1995 | 469185531.790448 ARGENTINA | 1994 | 469897712.259989 ARGENTINA | 1993 | 473622298.990221 ARGENTINA | 1992 | 470472182.370461 BRAZIL | 1998 | 272849051.882155 BRAZIL | 1997 | 463050262.005411 BRAZIL | 1996 | 475809102.8973 BRAZIL | 1995 | 468807809.383086 BRAZIL | 1994 | 461001368.595586 BRAZIL | 1993 | 462972602.578521 BRAZIL | 1992 | 471404873.32593 CANADA | 1998 | 274300271.415778 CANADA | 1997 | 476309700.642178 CANADA | 1996 | 476917771.1336 CANADA | 1995 | 475186145.464312 CANADA | 1994 | 476360108.585412 CANADA | 1993 | 473802969.004727 CANADA | 1992 | 479232261.551158 CHINA | 1998 | 268783599.690264 CHINA | 1997 | 462632622.307022 CHINA | 1996 | 466419704.141431 CHINA | 1995 | 464579562.049593 CHINA | 1994 | 465574998.202094 CHINA | 1993 | 467062112.834197 CHINA | 1992 | 470208635.294523 EGYPT | 1998 | 270438660.749559 EGYPT | 1997 | 465238712.406711 EGYPT | 1996 | 464518062.741882 EGYPT | 1995 | 457639424.920035 EGYPT | 1994 | 458730721.363011 EGYPT | 1993 | 470122957.482108 EGYPT | 1992 | 462600240.85728 ETHIOPIA | 1998 | 264687242.94706 ETHIOPIA | 1997 | 464210266.290262 ETHIOPIA | 1996 | 465759027.373626 ETHIOPIA | 1995 | 465285056.127085 ETHIOPIA | 1994 | 461968142.528193 ETHIOPIA | 1993 | 458286242.1144 ETHIOPIA | 1992 | 469410841.95295 FRANCE | 1998 | 263244698.635753 FRANCE | 1997 | 438678936.586067 FRANCE | 1996 | 458067088.090648 FRANCE | 1995 | 449323894.305477 FRANCE | 1994 | 452696758.580311 FRANCE | 1993 | 458984272.875964 FRANCE | 1992 | 448253678.128284 GERMANY | 1998 | 277682522.311296 GERMANY | 1997 | 477634499.805178 GERMANY | 1996 | 473245466.257332 GERMANY | 1995 | 474387429.19717 GERMANY | 1994 | 474681740.537054 GERMANY | 1993 | 482419819.371204 GERMANY | 1992 | 473503725.727591 INDIA | 1998 | 284181500.848001 INDIA | 1997 | 482617484.048299 INDIA | 1996 | 485682051.844768 INDIA | 1995 | 481524282.842479 INDIA | 1994 | 491095878.249857 INDIA | 1993 | 479914251.952703 INDIA | 1992 | 485535784.818029 INDONESIA | 1998 | 272732617.269211 INDONESIA | 1997 | 463507714.120993 INDONESIA | 1996 | 471269151.767262 INDONESIA | 1995 | 465915830.526208 INDONESIA | 1994 | 460136388.355915 INDONESIA | 1993 | 473373885.971198 INDONESIA | 1992 | 463820642.399931 IRAN | 1998 | 270392980.27919 IRAN | 1997 | 456320576.242962 IRAN | 1996 | 454154402.261446 IRAN | 1995 | 457369990.145245 IRAN | 1994 | 457359900.09228 IRAN | 1993 | 460121216.394956 IRAN | 1992 | 458782049.351427 IRAQ | 1998 | 276809830.530352 IRAQ | 1997 | 473597788.789764 IRAQ | 1996 | 473025348.720441 IRAQ | 1995 | 474049727.543073 IRAQ | 1994 | 465950857.483737 IRAQ | 1993 | 473134275.488682 IRAQ | 1992 | 468241678.236094 JAPAN | 1998 | 274647744.294243 JAPAN | 1997 | 461690487.77691 JAPAN | 1996 | 471639230.667504 JAPAN | 1995 | 460901224.238964 JAPAN | 1994 | 476404748.848279 JAPAN | 1993 | 466087255.697695 JAPAN | 1992 | 468219801.474072 JORDAN | 1998 | 269707306.918141 JORDAN | 1997 | 460381017.971061 JORDAN | 1996 | 461270672.870603 JORDAN | 1995 | 459500698.72564 JORDAN | 1994 | 457587949.671363 JORDAN | 1993 | 464937542.945141 JORDAN | 1992 | 464871433.483881 KENYA | 1998 | 275545280.644851 KENYA | 1997 | 470604588.725321 KENYA | 1996 | 472776721.895501 KENYA | 1995 | 473495894.878403 KENYA | 1994 | 463907931.747795 KENYA | 1993 | 468464247.217299 KENYA | 1992 | 470732582.447288 MOROCCO | 1998 | 275341649.526257 MOROCCO | 1997 | 466879763.03373 MOROCCO | 1996 | 467210878.04352 MOROCCO | 1995 | 475915822.683841 MOROCCO | 1994 | 469313085.917285 MOROCCO | 1993 | 472932355.658352 MOROCCO | 1992 | 469178038.013003 MOZAMBIQUE | 1998 | 271838214.6931 MOZAMBIQUE | 1997 | 465032064.127429 MOZAMBIQUE | 1996 | 466669968.925492 MOZAMBIQUE | 1995 | 466221150.573149 MOZAMBIQUE | 1994 | 456304766.755928 MOZAMBIQUE | 1993 | 460218125.914235 MOZAMBIQUE | 1992 | 454648937.608948 PERU | 1998 | 274424383.770746 PERU | 1997 | 468453335.923072 PERU | 1996 | 464548124.150788 PERU | 1995 | 464707983.749916 PERU | 1994 | 463931503.07491 PERU | 1993 | 459625029.248676 PERU | 1992 | 468832847.755434 ROMANIA | 1998 | 266853216.705904 ROMANIA | 1997 | 461741895.935794 ROMANIA | 1996 | 467726846.271736 ROMANIA | 1995 | 465938037.677417 ROMANIA | 1994 | 467292474.350974 ROMANIA | 1993 | 462678219.93316 ROMANIA | 1992 | 468825527.655734 RUSSIA | 1998 | 268954565.128431 RUSSIA | 1997 | 466862340.170682 RUSSIA | 1996 | 468491773.446265 RUSSIA | 1995 | 469866971.483887 RUSSIA | 1994 | 469071994.086797 RUSSIA | 1993 | 460999627.865693 RUSSIA | 1992 | 466914430.75231 SAUDI ARABIA | 1998 | 272541435.502519 SAUDI ARABIA | 1997 | 465919523.304403 SAUDI ARABIA | 1996 | 464440157.736374 SAUDI ARABIA | 1995 | 470488227.210947 SAUDI ARABIA | 1994 | 460203492.49936 SAUDI ARABIA | 1993 | 469022629.970909 SAUDI ARABIA | 1992 | 466619696.387112 UNITED KINGDOM | 1998 | 266568429.833556 UNITED KINGDOM | 1997 | 470175470.606925 UNITED KINGDOM | 1996 | 464385355.400549 UNITED KINGDOM | 1995 | 454360432.326754 UNITED KINGDOM | 1994 | 458185374.689166 UNITED KINGDOM | 1993 | 467368295.209551 UNITED KINGDOM | 1992 | 458356712.821993 UNITED STATES | 1998 | 280017681.541936 UNITED STATES | 1997 | 476606262.236136 UNITED STATES | 1996 | 479733951.846978 UNITED STATES | 1995 | 470347899.926174 UNITED STATES | 1994 | 476835836.777498 UNITED STATES | 1993 | 479738221.212997 UNITED STATES | 1992 | 467549946.045403 VIETNAM | 1998 | 269272647.550483 VIETNAM | 1997 | 463234678.033313 VIETNAM | 1996 | 474125014.287626 VIETNAM | 1995 | 463050325.955811 VIETNAM | 1994 | 471205923.176144 VIETNAM | 1993 | 468023251.175761 VIETNAM | 1992 | 474585359.569835 (175 rows) COMMIT; COMMIT