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 '%forest%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264122628.886785 ALGERIA | 1997 | 462026285.925636 ALGERIA | 1996 | 454872634.94362 ALGERIA | 1995 | 453739275.772731 ALGERIA | 1994 | 457395181.937354 ALGERIA | 1993 | 460634537.624511 ALGERIA | 1992 | 458173994.21082 ARGENTINA | 1998 | 274501171.176667 ARGENTINA | 1997 | 465234655.543179 ARGENTINA | 1996 | 471320482.008848 ARGENTINA | 1995 | 477408672.702099 ARGENTINA | 1994 | 466536729.881347 ARGENTINA | 1993 | 467715396.599747 ARGENTINA | 1992 | 472668110.219993 BRAZIL | 1998 | 270534706.442247 BRAZIL | 1997 | 471041781.04689 BRAZIL | 1996 | 469498419.698697 BRAZIL | 1995 | 478974822.285893 BRAZIL | 1994 | 469299466.224524 BRAZIL | 1993 | 467984937.578396 BRAZIL | 1992 | 465166449.955107 CANADA | 1998 | 269428718.432677 CANADA | 1997 | 470784158.249633 CANADA | 1996 | 473594214.172929 CANADA | 1995 | 469977401.447933 CANADA | 1994 | 475982603.308006 CANADA | 1993 | 464315503.948099 CANADA | 1992 | 474255545.523946 CHINA | 1998 | 274861339.438113 CHINA | 1997 | 476371385.549144 CHINA | 1996 | 470973065.86746 CHINA | 1995 | 465425699.430391 CHINA | 1994 | 476263768.725945 CHINA | 1993 | 479773127.137698 CHINA | 1992 | 473870480.587075 EGYPT | 1998 | 276641238.207752 EGYPT | 1997 | 461617587.565629 EGYPT | 1996 | 463715290.26274 EGYPT | 1995 | 462666089.62627 EGYPT | 1994 | 464215816.082108 EGYPT | 1993 | 461319120.926266 EGYPT | 1992 | 464250299.114336 ETHIOPIA | 1998 | 267271325.215372 ETHIOPIA | 1997 | 464722586.172899 ETHIOPIA | 1996 | 454883753.92573 ETHIOPIA | 1995 | 460838865.923795 ETHIOPIA | 1994 | 460999848.899351 ETHIOPIA | 1993 | 456947892.597544 ETHIOPIA | 1992 | 463725160.390586 FRANCE | 1998 | 272944733.391638 FRANCE | 1997 | 463501581.674 FRANCE | 1996 | 462566178.625267 FRANCE | 1995 | 464086174.43435 FRANCE | 1994 | 462234561.489775 FRANCE | 1993 | 453598071.553625 FRANCE | 1992 | 463272186.875315 GERMANY | 1998 | 278766041.608737 GERMANY | 1997 | 485236329.499212 GERMANY | 1996 | 477121847.177961 GERMANY | 1995 | 473431816.589849 GERMANY | 1994 | 468258593.095657 GERMANY | 1993 | 470532216.19023 GERMANY | 1992 | 474725982.138336 INDIA | 1998 | 274327378.133222 INDIA | 1997 | 470308171.147459 INDIA | 1996 | 481157845.893497 INDIA | 1995 | 481487046.966139 INDIA | 1994 | 475219737.180984 INDIA | 1993 | 476851162.640146 INDIA | 1992 | 480806672.794498 INDONESIA | 1998 | 278872824.787159 INDONESIA | 1997 | 470694257.94395 INDONESIA | 1996 | 477742147.610312 INDONESIA | 1995 | 471790926.106108 INDONESIA | 1994 | 477785198.23196 INDONESIA | 1993 | 471744883.030301 INDONESIA | 1992 | 472846051.445828 IRAN | 1998 | 271898306.900989 IRAN | 1997 | 471857169.998362 IRAN | 1996 | 464187220.001249 IRAN | 1995 | 464644239.832235 IRAN | 1994 | 471135737.923241 IRAN | 1993 | 468368931.278117 IRAN | 1992 | 473517167.244321 IRAQ | 1998 | 272464189.636285 IRAQ | 1997 | 460563198.596876 IRAQ | 1996 | 469676348.606673 IRAQ | 1995 | 458959080.00044 IRAQ | 1994 | 465248235.895727 IRAQ | 1993 | 464876226.248838 IRAQ | 1992 | 464290969.736902 JAPAN | 1998 | 274495837.308758 JAPAN | 1997 | 469488562.650242 JAPAN | 1996 | 459200529.562187 JAPAN | 1995 | 459854833.631722 JAPAN | 1994 | 468620044.979953 JAPAN | 1993 | 468011177.249795 JAPAN | 1992 | 464459385.293811 JORDAN | 1998 | 269071608.708955 JORDAN | 1997 | 454025684.065664 JORDAN | 1996 | 463026620.093056 JORDAN | 1995 | 463457804.622234 JORDAN | 1994 | 467247167.862206 JORDAN | 1993 | 463205658.010581 JORDAN | 1992 | 463745683.88438 KENYA | 1998 | 272187471.575357 KENYA | 1997 | 476822203.317759 KENYA | 1996 | 471630949.113181 KENYA | 1995 | 476875174.60794 KENYA | 1994 | 471786623.396441 KENYA | 1993 | 476266832.407114 KENYA | 1992 | 472567285.629311 MOROCCO | 1998 | 271563720.743256 MOROCCO | 1997 | 466374301.430991 MOROCCO | 1996 | 454696000.399382 MOROCCO | 1995 | 466632318.475702 MOROCCO | 1994 | 463838491.6658 MOROCCO | 1993 | 469766245.955544 MOROCCO | 1992 | 470047359.095468 MOZAMBIQUE | 1998 | 260694207.73705 MOZAMBIQUE | 1997 | 448114365.031154 MOZAMBIQUE | 1996 | 455655649.935797 MOZAMBIQUE | 1995 | 459274659.333265 MOZAMBIQUE | 1994 | 456463344.94058 MOZAMBIQUE | 1993 | 454579340.060562 MOZAMBIQUE | 1992 | 461280655.26387 PERU | 1998 | 273900992.626896 PERU | 1997 | 465285151.60189 PERU | 1996 | 470862377.066406 PERU | 1995 | 465873320.96561 PERU | 1994 | 463290038.931396 PERU | 1993 | 464909169.646029 PERU | 1992 | 463816860.758869 ROMANIA | 1998 | 274248897.952146 ROMANIA | 1997 | 465983636.556378 ROMANIA | 1996 | 467177380.771799 ROMANIA | 1995 | 469759997.126476 ROMANIA | 1994 | 474912191.990441 ROMANIA | 1993 | 464039916.844301 ROMANIA | 1992 | 473984174.706465 RUSSIA | 1998 | 275883542.205634 RUSSIA | 1997 | 465240301.835589 RUSSIA | 1996 | 471561568.344707 RUSSIA | 1995 | 463456344.023144 RUSSIA | 1994 | 464273114.248831 RUSSIA | 1993 | 467320856.350096 RUSSIA | 1992 | 459640872.296553 SAUDI ARABIA | 1998 | 270720731.834947 SAUDI ARABIA | 1997 | 466146371.046601 SAUDI ARABIA | 1996 | 473836715.193179 SAUDI ARABIA | 1995 | 471142929.40031 SAUDI ARABIA | 1994 | 470929991.099796 SAUDI ARABIA | 1993 | 462498210.298103 SAUDI ARABIA | 1992 | 464612792.716792 UNITED KINGDOM | 1998 | 266395388.280041 UNITED KINGDOM | 1997 | 462414596.312336 UNITED KINGDOM | 1996 | 459630526.47498 UNITED KINGDOM | 1995 | 458681580.316874 UNITED KINGDOM | 1994 | 460588633.354797 UNITED KINGDOM | 1993 | 457355245.706865 UNITED KINGDOM | 1992 | 460722311.612627 UNITED STATES | 1998 | 277257681.279741 UNITED STATES | 1997 | 475176667.352816 UNITED STATES | 1996 | 470013033.733308 UNITED STATES | 1995 | 467874968.791667 UNITED STATES | 1994 | 477485358.56435 UNITED STATES | 1993 | 465275293.77023 UNITED STATES | 1992 | 473161789.713271 VIETNAM | 1998 | 271983835.903793 VIETNAM | 1997 | 458792805.775022 VIETNAM | 1996 | 462148885.476104 VIETNAM | 1995 | 454058057.539673 VIETNAM | 1994 | 454605763.629203 VIETNAM | 1993 | 459480493.228548 VIETNAM | 1992 | 456315270.0615 (175 rows) COMMIT; COMMIT