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 '%plum%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266736278.852698 ALGERIA | 1997 | 446159757.939524 ALGERIA | 1996 | 463232284.689942 ALGERIA | 1995 | 459515754.894107 ALGERIA | 1994 | 457192982.302479 ALGERIA | 1993 | 462843124.140849 ALGERIA | 1992 | 460527041.504166 ARGENTINA | 1998 | 269709449.224603 ARGENTINA | 1997 | 467212838.070497 ARGENTINA | 1996 | 466423137.720129 ARGENTINA | 1995 | 460928598.503259 ARGENTINA | 1994 | 465445120.561865 ARGENTINA | 1993 | 465218720.28226 ARGENTINA | 1992 | 456197719.665955 BRAZIL | 1998 | 273285222.904864 BRAZIL | 1997 | 482216723.043079 BRAZIL | 1996 | 473144055.695784 BRAZIL | 1995 | 467681068.235591 BRAZIL | 1994 | 468978088.054839 BRAZIL | 1993 | 482768661.536474 BRAZIL | 1992 | 473868709.161393 CANADA | 1998 | 270996052.538505 CANADA | 1997 | 468210516.303544 CANADA | 1996 | 475362198.969601 CANADA | 1995 | 473193129.828594 CANADA | 1994 | 482626615.514465 CANADA | 1993 | 478957912.093032 CANADA | 1992 | 472086768.553861 CHINA | 1998 | 273402809.344655 CHINA | 1997 | 465477470.89116 CHINA | 1996 | 475565827.287892 CHINA | 1995 | 472390978.461341 CHINA | 1994 | 475289108.513111 CHINA | 1993 | 467388926.616161 CHINA | 1992 | 468641284.98563 EGYPT | 1998 | 274015966.489491 EGYPT | 1997 | 464452728.536684 EGYPT | 1996 | 471043724.262397 EGYPT | 1995 | 463624008.471916 EGYPT | 1994 | 454334818.741908 EGYPT | 1993 | 461224039.770652 EGYPT | 1992 | 465392497.439737 ETHIOPIA | 1998 | 268188148.438057 ETHIOPIA | 1997 | 456184401.778985 ETHIOPIA | 1996 | 466127469.736449 ETHIOPIA | 1995 | 465397677.717904 ETHIOPIA | 1994 | 463723299.022068 ETHIOPIA | 1993 | 456401959.578664 ETHIOPIA | 1992 | 467362237.986485 FRANCE | 1998 | 270925734.220919 FRANCE | 1997 | 463148246.746285 FRANCE | 1996 | 466729984.2708 FRANCE | 1995 | 469970077.348009 FRANCE | 1994 | 464458531.220901 FRANCE | 1993 | 465208831.527133 FRANCE | 1992 | 463857375.849119 GERMANY | 1998 | 272557106.678733 GERMANY | 1997 | 463132777.173546 GERMANY | 1996 | 464927833.075693 GERMANY | 1995 | 468741834.373986 GERMANY | 1994 | 468865112.64075 GERMANY | 1993 | 468611622.916549 GERMANY | 1992 | 472220331.040143 INDIA | 1998 | 270831068.768118 INDIA | 1997 | 468329164.022997 INDIA | 1996 | 477141510.998137 INDIA | 1995 | 475696012.22546 INDIA | 1994 | 466621870.746036 INDIA | 1993 | 470286924.205137 INDIA | 1992 | 478006170.862071 INDONESIA | 1998 | 271438969.587729 INDONESIA | 1997 | 467741764.67244 INDONESIA | 1996 | 463112059.655239 INDONESIA | 1995 | 461819686.55777 INDONESIA | 1994 | 458676521.946784 INDONESIA | 1993 | 465198816.589694 INDONESIA | 1992 | 468160213.162384 IRAN | 1998 | 274483037.957747 IRAN | 1997 | 468268456.773092 IRAN | 1996 | 464375658.618509 IRAN | 1995 | 468649517.593112 IRAN | 1994 | 464169325.965894 IRAN | 1993 | 463215891.640963 IRAN | 1992 | 461005483.129888 IRAQ | 1998 | 271405572.382109 IRAQ | 1997 | 468609747.500391 IRAQ | 1996 | 481630015.900397 IRAQ | 1995 | 459887169.167384 IRAQ | 1994 | 470167595.65992 IRAQ | 1993 | 474017165.487252 IRAQ | 1992 | 466208803.748742 JAPAN | 1998 | 274166192.261574 JAPAN | 1997 | 469247320.932534 JAPAN | 1996 | 471028834.698911 JAPAN | 1995 | 466576163.001918 JAPAN | 1994 | 458614665.854252 JAPAN | 1993 | 467526286.439341 JAPAN | 1992 | 461053085.678567 JORDAN | 1998 | 269329815.806212 JORDAN | 1997 | 469419749.908464 JORDAN | 1996 | 462749917.620441 JORDAN | 1995 | 462394509.484653 JORDAN | 1994 | 457884835.157786 JORDAN | 1993 | 471201885.241509 JORDAN | 1992 | 463443162.718413 KENYA | 1998 | 280925194.469725 KENYA | 1997 | 473099422.816009 KENYA | 1996 | 475063123.436065 KENYA | 1995 | 476974985.528209 KENYA | 1994 | 480439269.431096 KENYA | 1993 | 474859319.975026 KENYA | 1992 | 477417484.387986 MOROCCO | 1998 | 276135620.422056 MOROCCO | 1997 | 471250407.599658 MOROCCO | 1996 | 462548670.06869 MOROCCO | 1995 | 463781333.412611 MOROCCO | 1994 | 470393131.140191 MOROCCO | 1993 | 463205545.031665 MOROCCO | 1992 | 464684305.146028 MOZAMBIQUE | 1998 | 265937474.596375 MOZAMBIQUE | 1997 | 454798395.292333 MOZAMBIQUE | 1996 | 453705826.367514 MOZAMBIQUE | 1995 | 449128566.195502 MOZAMBIQUE | 1994 | 450848734.573888 MOZAMBIQUE | 1993 | 463287364.142296 MOZAMBIQUE | 1992 | 459471017.71714 PERU | 1998 | 271637740.059697 PERU | 1997 | 460666919.113444 PERU | 1996 | 459072884.60504 PERU | 1995 | 454733721.144751 PERU | 1994 | 455429838.886921 PERU | 1993 | 463281708.700863 PERU | 1992 | 463197451.484955 ROMANIA | 1998 | 275374281.61926 ROMANIA | 1997 | 456545443.648585 ROMANIA | 1996 | 463958804.987079 ROMANIA | 1995 | 468034890.391359 ROMANIA | 1994 | 469034200.404455 ROMANIA | 1993 | 459294764.225687 ROMANIA | 1992 | 455673935.434263 RUSSIA | 1998 | 277534646.341765 RUSSIA | 1997 | 475459736.24423 RUSSIA | 1996 | 470928871.095487 RUSSIA | 1995 | 477077152.400335 RUSSIA | 1994 | 471505568.332081 RUSSIA | 1993 | 463291768.007954 RUSSIA | 1992 | 467285071.786709 SAUDI ARABIA | 1998 | 272787822.664025 SAUDI ARABIA | 1997 | 467788049.104726 SAUDI ARABIA | 1996 | 467895213.581845 SAUDI ARABIA | 1995 | 468600807.768034 SAUDI ARABIA | 1994 | 462418691.417933 SAUDI ARABIA | 1993 | 464811254.774656 SAUDI ARABIA | 1992 | 471328708.909097 UNITED KINGDOM | 1998 | 267699203.768601 UNITED KINGDOM | 1997 | 465492821.326575 UNITED KINGDOM | 1996 | 459335804.719582 UNITED KINGDOM | 1995 | 462223959.742235 UNITED KINGDOM | 1994 | 455080803.858558 UNITED KINGDOM | 1993 | 460889266.897116 UNITED KINGDOM | 1992 | 463856771.426226 UNITED STATES | 1998 | 276726411.6795 UNITED STATES | 1997 | 480782935.246432 UNITED STATES | 1996 | 473419744.50208 UNITED STATES | 1995 | 472142296.089685 UNITED STATES | 1994 | 472838221.11103 UNITED STATES | 1993 | 473034546.431344 UNITED STATES | 1992 | 471286723.061314 VIETNAM | 1998 | 274595845.766553 VIETNAM | 1997 | 467412137.533954 VIETNAM | 1996 | 480669241.671984 VIETNAM | 1995 | 466918471.43677 VIETNAM | 1994 | 475544512.876006 VIETNAM | 1993 | 476304208.799103 VIETNAM | 1992 | 471473420.45168 (175 rows) COMMIT; COMMIT