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 '%dim%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271182638.470318 ALGERIA | 1997 | 461555953.094363 ALGERIA | 1996 | 467355221.124883 ALGERIA | 1995 | 456063275.227368 ALGERIA | 1994 | 455247200.686511 ALGERIA | 1993 | 460675275.517456 ALGERIA | 1992 | 456113586.926989 ARGENTINA | 1998 | 275015481.035401 ARGENTINA | 1997 | 477136108.113654 ARGENTINA | 1996 | 469961962.360383 ARGENTINA | 1995 | 474960708.118854 ARGENTINA | 1994 | 470392972.044643 ARGENTINA | 1993 | 467506896.909618 ARGENTINA | 1992 | 470850577.718259 BRAZIL | 1998 | 270117494.028977 BRAZIL | 1997 | 462308730.612769 BRAZIL | 1996 | 463385313.062478 BRAZIL | 1995 | 462010587.794822 BRAZIL | 1994 | 460104495.734619 BRAZIL | 1993 | 465731591.686423 BRAZIL | 1992 | 464000291.913391 CANADA | 1998 | 276704268.57954 CANADA | 1997 | 467863434.35311 CANADA | 1996 | 476239122.671635 CANADA | 1995 | 472954349.538922 CANADA | 1994 | 475614823.921197 CANADA | 1993 | 469510059.633887 CANADA | 1992 | 476155468.931998 CHINA | 1998 | 275601370.09235 CHINA | 1997 | 469341190.266305 CHINA | 1996 | 467909241.240092 CHINA | 1995 | 470995800.622621 CHINA | 1994 | 463422695.354614 CHINA | 1993 | 467349998.773107 CHINA | 1992 | 471061255.782838 EGYPT | 1998 | 277775408.274405 EGYPT | 1997 | 472893518.444989 EGYPT | 1996 | 474503086.001009 EGYPT | 1995 | 463322560.464971 EGYPT | 1994 | 466785203.05126 EGYPT | 1993 | 468211565.641196 EGYPT | 1992 | 468105602.730146 ETHIOPIA | 1998 | 266388007.309005 ETHIOPIA | 1997 | 454274828.973665 ETHIOPIA | 1996 | 454966116.919795 ETHIOPIA | 1995 | 455868262.802163 ETHIOPIA | 1994 | 453480772.049737 ETHIOPIA | 1993 | 459985863.60319 ETHIOPIA | 1992 | 463049220.894881 FRANCE | 1998 | 264971500.853563 FRANCE | 1997 | 459047565.110797 FRANCE | 1996 | 454102124.992641 FRANCE | 1995 | 455044582.806792 FRANCE | 1994 | 456547270.759633 FRANCE | 1993 | 457440801.888348 FRANCE | 1992 | 451179895.328067 GERMANY | 1998 | 280513496.367972 GERMANY | 1997 | 474654491.276856 GERMANY | 1996 | 474394594.811691 GERMANY | 1995 | 475605777.210906 GERMANY | 1994 | 478175095.362214 GERMANY | 1993 | 479966797.883723 GERMANY | 1992 | 477142295.749146 INDIA | 1998 | 271873867.322234 INDIA | 1997 | 481487307.451371 INDIA | 1996 | 479202059.897932 INDIA | 1995 | 478507878.991014 INDIA | 1994 | 472295245.454437 INDIA | 1993 | 471477896.217036 INDIA | 1992 | 478426119.251907 INDONESIA | 1998 | 271845423.657972 INDONESIA | 1997 | 464366478.133852 INDONESIA | 1996 | 472437813.046923 INDONESIA | 1995 | 468790829.182291 INDONESIA | 1994 | 460506138.792049 INDONESIA | 1993 | 472585716.818886 INDONESIA | 1992 | 469030852.679602 IRAN | 1998 | 273356982.440257 IRAN | 1997 | 465443524.010415 IRAN | 1996 | 453856518.564815 IRAN | 1995 | 457334457.305684 IRAN | 1994 | 457824266.515423 IRAN | 1993 | 454302331.057918 IRAN | 1992 | 457369000.35853 IRAQ | 1998 | 274159609.392339 IRAQ | 1997 | 473923184.468655 IRAQ | 1996 | 472485710.456778 IRAQ | 1995 | 474555096.774608 IRAQ | 1994 | 472382109.473655 IRAQ | 1993 | 472714955.155998 IRAQ | 1992 | 476279853.721877 JAPAN | 1998 | 269655203.047328 JAPAN | 1997 | 465926168.189802 JAPAN | 1996 | 465537834.894214 JAPAN | 1995 | 469040972.34179 JAPAN | 1994 | 455848097.064569 JAPAN | 1993 | 463968945.425066 JAPAN | 1992 | 460309241.471827 JORDAN | 1998 | 275617803.731944 JORDAN | 1997 | 460356892.597049 JORDAN | 1996 | 458315889.8568 JORDAN | 1995 | 456504180.964923 JORDAN | 1994 | 455691137.965006 JORDAN | 1993 | 456167900.411669 JORDAN | 1992 | 462002707.406475 KENYA | 1998 | 273755036.162173 KENYA | 1997 | 467077348.739518 KENYA | 1996 | 473233802.566562 KENYA | 1995 | 465947411.036649 KENYA | 1994 | 467752800.247958 KENYA | 1993 | 473114526.846272 KENYA | 1992 | 473559058.711764 MOROCCO | 1998 | 275467095.086422 MOROCCO | 1997 | 470559918.621145 MOROCCO | 1996 | 463960470.570129 MOROCCO | 1995 | 468272734.305691 MOROCCO | 1994 | 470469771.245806 MOROCCO | 1993 | 464067152.283406 MOROCCO | 1992 | 468984342.212899 MOZAMBIQUE | 1998 | 275093904.246218 MOZAMBIQUE | 1997 | 469775067.210101 MOZAMBIQUE | 1996 | 467004496.794531 MOZAMBIQUE | 1995 | 471099312.793173 MOZAMBIQUE | 1994 | 459160778.804068 MOZAMBIQUE | 1993 | 464150631.803663 MOZAMBIQUE | 1992 | 461285087.177139 PERU | 1998 | 275574619.004305 PERU | 1997 | 468235506.170247 PERU | 1996 | 461399592.745646 PERU | 1995 | 459108918.750965 PERU | 1994 | 462740414.737605 PERU | 1993 | 464859723.42922 PERU | 1992 | 468396577.709063 ROMANIA | 1998 | 276719768.979386 ROMANIA | 1997 | 466761389.952625 ROMANIA | 1996 | 473665499.186153 ROMANIA | 1995 | 466818051.200756 ROMANIA | 1994 | 471535625.686543 ROMANIA | 1993 | 471901813.337497 ROMANIA | 1992 | 471591534.414993 RUSSIA | 1998 | 277930325.195377 RUSSIA | 1997 | 473192848.441898 RUSSIA | 1996 | 475517338.754868 RUSSIA | 1995 | 477889445.971296 RUSSIA | 1994 | 472745795.601216 RUSSIA | 1993 | 472218117.383081 RUSSIA | 1992 | 481266311.744865 SAUDI ARABIA | 1998 | 271182517.748675 SAUDI ARABIA | 1997 | 458363498.63736 SAUDI ARABIA | 1996 | 461407797.715429 SAUDI ARABIA | 1995 | 462657369.776886 SAUDI ARABIA | 1994 | 462476996.889509 SAUDI ARABIA | 1993 | 467220444.389689 SAUDI ARABIA | 1992 | 475388748.027708 UNITED KINGDOM | 1998 | 273126613.200052 UNITED KINGDOM | 1997 | 463384172.790599 UNITED KINGDOM | 1996 | 463592841.88937 UNITED KINGDOM | 1995 | 463815859.329831 UNITED KINGDOM | 1994 | 466833967.450834 UNITED KINGDOM | 1993 | 461100545.091616 UNITED KINGDOM | 1992 | 463525961.976568 UNITED STATES | 1998 | 283982170.408048 UNITED STATES | 1997 | 474725304.170388 UNITED STATES | 1996 | 477593579.647375 UNITED STATES | 1995 | 475002937.44632 UNITED STATES | 1994 | 476177347.82015 UNITED STATES | 1993 | 480906981.217602 UNITED STATES | 1992 | 485727408.017801 VIETNAM | 1998 | 274832807.892053 VIETNAM | 1997 | 465149164.889632 VIETNAM | 1996 | 469812294.094944 VIETNAM | 1995 | 466344905.701007 VIETNAM | 1994 | 465973610.9046 VIETNAM | 1993 | 475575012.463161 VIETNAM | 1992 | 465744376.569672 (175 rows) COMMIT; COMMIT