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.5035 ALGERIA | 1996 | 459810771.585019 ALGERIA | 1995 | 466383797.151038 ALGERIA | 1994 | 462440231.605041 ALGERIA | 1993 | 467681860.001185 ALGERIA | 1992 | 466823953.526656 ARGENTINA | 1998 | 275041667.502382 ARGENTINA | 1997 | 465462654.232565 ARGENTINA | 1996 | 472261430.04155 ARGENTINA | 1995 | 469185531.790448 ARGENTINA | 1994 | 469897712.25999 ARGENTINA | 1993 | 473622298.990218 ARGENTINA | 1992 | 470472182.370461 BRAZIL | 1998 | 272849051.882156 BRAZIL | 1997 | 463050262.005412 BRAZIL | 1996 | 475809102.897301 BRAZIL | 1995 | 468807809.383086 BRAZIL | 1994 | 461001368.595585 BRAZIL | 1993 | 462972602.578521 BRAZIL | 1992 | 471404873.325929 CANADA | 1998 | 274300271.415778 CANADA | 1997 | 476309700.64218 CANADA | 1996 | 476917771.133599 CANADA | 1995 | 475186145.464313 CANADA | 1994 | 476360108.585407 CANADA | 1993 | 473802969.004725 CANADA | 1992 | 479232261.551159 CHINA | 1998 | 268783599.690265 CHINA | 1997 | 462632622.307024 CHINA | 1996 | 466419704.14143 CHINA | 1995 | 464579562.049592 CHINA | 1994 | 465574998.202092 CHINA | 1993 | 467062112.834194 CHINA | 1992 | 470208635.294523 EGYPT | 1998 | 270438660.749559 EGYPT | 1997 | 465238712.406706 EGYPT | 1996 | 464518062.74188 EGYPT | 1995 | 457639424.920034 EGYPT | 1994 | 458730721.363011 EGYPT | 1993 | 470122957.482108 EGYPT | 1992 | 462600240.85728 ETHIOPIA | 1998 | 264687242.94706 ETHIOPIA | 1997 | 464210266.290265 ETHIOPIA | 1996 | 465759027.373623 ETHIOPIA | 1995 | 465285056.127085 ETHIOPIA | 1994 | 461968142.528194 ETHIOPIA | 1993 | 458286242.114397 ETHIOPIA | 1992 | 469410841.952952 FRANCE | 1998 | 263244698.635751 FRANCE | 1997 | 438678936.586066 FRANCE | 1996 | 458067088.09065 FRANCE | 1995 | 449323894.305478 FRANCE | 1994 | 452696758.580311 FRANCE | 1993 | 458984272.875964 FRANCE | 1992 | 448253678.128283 GERMANY | 1998 | 277682522.311294 GERMANY | 1997 | 477634499.805179 GERMANY | 1996 | 473245466.257333 GERMANY | 1995 | 474387429.197171 GERMANY | 1994 | 474681740.537054 GERMANY | 1993 | 482419819.371204 GERMANY | 1992 | 473503725.727593 INDIA | 1998 | 284181500.848003 INDIA | 1997 | 482617484.048299 INDIA | 1996 | 485682051.844767 INDIA | 1995 | 481524282.842476 INDIA | 1994 | 491095878.249855 INDIA | 1993 | 479914251.952704 INDIA | 1992 | 485535784.818031 INDONESIA | 1998 | 272732617.26921 INDONESIA | 1997 | 463507714.120995 INDONESIA | 1996 | 471269151.767265 INDONESIA | 1995 | 465915830.526207 INDONESIA | 1994 | 460136388.355915 INDONESIA | 1993 | 473373885.971198 INDONESIA | 1992 | 463820642.399932 IRAN | 1998 | 270392980.279189 IRAN | 1997 | 456320576.242962 IRAN | 1996 | 454154402.261449 IRAN | 1995 | 457369990.145245 IRAN | 1994 | 457359900.092285 IRAN | 1993 | 460121216.394956 IRAN | 1992 | 458782049.351429 IRAQ | 1998 | 276809830.530352 IRAQ | 1997 | 473597788.789767 IRAQ | 1996 | 473025348.72044 IRAQ | 1995 | 474049727.543072 IRAQ | 1994 | 465950857.483736 IRAQ | 1993 | 473134275.48868 IRAQ | 1992 | 468241678.236094 JAPAN | 1998 | 274647744.294242 JAPAN | 1997 | 461690487.77691 JAPAN | 1996 | 471639230.667506 JAPAN | 1995 | 460901224.238961 JAPAN | 1994 | 476404748.848278 JAPAN | 1993 | 466087255.697694 JAPAN | 1992 | 468219801.474072 JORDAN | 1998 | 269707306.918141 JORDAN | 1997 | 460381017.971058 JORDAN | 1996 | 461270672.870602 JORDAN | 1995 | 459500698.72564 JORDAN | 1994 | 457587949.67136 JORDAN | 1993 | 464937542.945144 JORDAN | 1992 | 464871433.483883 KENYA | 1998 | 275545280.644851 KENYA | 1997 | 470604588.725321 KENYA | 1996 | 472776721.8955 KENYA | 1995 | 473495894.878407 KENYA | 1994 | 463907931.747793 KENYA | 1993 | 468464247.217301 KENYA | 1992 | 470732582.447288 MOROCCO | 1998 | 275341649.526257 MOROCCO | 1997 | 466879763.033728 MOROCCO | 1996 | 467210878.043524 MOROCCO | 1995 | 475915822.683839 MOROCCO | 1994 | 469313085.917288 MOROCCO | 1993 | 472932355.658351 MOROCCO | 1992 | 469178038.013003 MOZAMBIQUE | 1998 | 271838214.693099 MOZAMBIQUE | 1997 | 465032064.127431 MOZAMBIQUE | 1996 | 466669968.92549 MOZAMBIQUE | 1995 | 466221150.573149 MOZAMBIQUE | 1994 | 456304766.755927 MOZAMBIQUE | 1993 | 460218125.914236 MOZAMBIQUE | 1992 | 454648937.608943 PERU | 1998 | 274424383.770748 PERU | 1997 | 468453335.92307 PERU | 1996 | 464548124.15079 PERU | 1995 | 464707983.749915 PERU | 1994 | 463931503.074909 PERU | 1993 | 459625029.248674 PERU | 1992 | 468832847.755432 ROMANIA | 1998 | 266853216.705904 ROMANIA | 1997 | 461741895.935794 ROMANIA | 1996 | 467726846.271736 ROMANIA | 1995 | 465938037.677417 ROMANIA | 1994 | 467292474.350977 ROMANIA | 1993 | 462678219.933159 ROMANIA | 1992 | 468825527.655733 RUSSIA | 1998 | 268954565.128431 RUSSIA | 1997 | 466862340.17068 RUSSIA | 1996 | 468491773.446267 RUSSIA | 1995 | 469866971.483884 RUSSIA | 1994 | 469071994.086795 RUSSIA | 1993 | 460999627.865692 RUSSIA | 1992 | 466914430.75231 SAUDI ARABIA | 1998 | 272541435.502517 SAUDI ARABIA | 1997 | 465919523.304403 SAUDI ARABIA | 1996 | 464440157.736376 SAUDI ARABIA | 1995 | 470488227.21095 SAUDI ARABIA | 1994 | 460203492.499357 SAUDI ARABIA | 1993 | 469022629.970909 SAUDI ARABIA | 1992 | 466619696.387115 UNITED KINGDOM | 1998 | 266568429.833557 UNITED KINGDOM | 1997 | 470175470.606923 UNITED KINGDOM | 1996 | 464385355.40055 UNITED KINGDOM | 1995 | 454360432.326753 UNITED KINGDOM | 1994 | 458185374.689167 UNITED KINGDOM | 1993 | 467368295.209549 UNITED KINGDOM | 1992 | 458356712.821993 UNITED STATES | 1998 | 280017681.541934 UNITED STATES | 1997 | 476606262.236135 UNITED STATES | 1996 | 479733951.846977 UNITED STATES | 1995 | 470347899.926173 UNITED STATES | 1994 | 476835836.777499 UNITED STATES | 1993 | 479738221.212997 UNITED STATES | 1992 | 467549946.045401 VIETNAM | 1998 | 269272647.550482 VIETNAM | 1997 | 463234678.03331 VIETNAM | 1996 | 474125014.287624 VIETNAM | 1995 | 463050325.95581 VIETNAM | 1994 | 471205923.176143 VIETNAM | 1993 | 468023251.175763 VIETNAM | 1992 | 474585359.569837 (175 rows) COMMIT; COMMIT