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 '%puff%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261437897.514405 ALGERIA | 1997 | 454185156.374892 ALGERIA | 1996 | 454416682.354875 ALGERIA | 1995 | 445705284.21545 ALGERIA | 1994 | 451423561.440995 ALGERIA | 1993 | 451990759.824075 ALGERIA | 1992 | 455672660.336977 ARGENTINA | 1998 | 273518314.174175 ARGENTINA | 1997 | 469580138.435826 ARGENTINA | 1996 | 478085269.291798 ARGENTINA | 1995 | 471490973.079707 ARGENTINA | 1994 | 464041811.55378 ARGENTINA | 1993 | 471266119.357607 ARGENTINA | 1992 | 466569807.574377 BRAZIL | 1998 | 273074056.569588 BRAZIL | 1997 | 469338412.534102 BRAZIL | 1996 | 461879774.313898 BRAZIL | 1995 | 463911168.995308 BRAZIL | 1994 | 466227561.142691 BRAZIL | 1993 | 465512348.349947 BRAZIL | 1992 | 478149161.248118 CANADA | 1998 | 277952748.182556 CANADA | 1997 | 476307347.123982 CANADA | 1996 | 476685096.722991 CANADA | 1995 | 478487560.026027 CANADA | 1994 | 479262540.589426 CANADA | 1993 | 470067223.197813 CANADA | 1992 | 475509077.732104 CHINA | 1998 | 273302820.276683 CHINA | 1997 | 463723423.007288 CHINA | 1996 | 459522652.095204 CHINA | 1995 | 455628056.102536 CHINA | 1994 | 460686566.665392 CHINA | 1993 | 464899285.468582 CHINA | 1992 | 462632863.428175 EGYPT | 1998 | 265672287.340583 EGYPT | 1997 | 457188729.486842 EGYPT | 1996 | 464262787.341033 EGYPT | 1995 | 459180302.141432 EGYPT | 1994 | 461145150.237152 EGYPT | 1993 | 451693999.406797 EGYPT | 1992 | 457529761.609346 ETHIOPIA | 1998 | 268852896.238212 ETHIOPIA | 1997 | 464908453.233355 ETHIOPIA | 1996 | 463068916.172226 ETHIOPIA | 1995 | 469948136.008248 ETHIOPIA | 1994 | 466555683.39399 ETHIOPIA | 1993 | 467500591.188847 ETHIOPIA | 1992 | 470870250.97174 FRANCE | 1998 | 266476815.6556 FRANCE | 1997 | 449486653.026556 FRANCE | 1996 | 460864084.824192 FRANCE | 1995 | 461466998.377693 FRANCE | 1994 | 460335127.085906 FRANCE | 1993 | 460440026.811035 FRANCE | 1992 | 457713471.797036 GERMANY | 1998 | 281538928.214327 GERMANY | 1997 | 473616571.790414 GERMANY | 1996 | 476540345.346889 GERMANY | 1995 | 474689436.423331 GERMANY | 1994 | 482012075.308719 GERMANY | 1993 | 473559267.491786 GERMANY | 1992 | 469437570.878045 INDIA | 1998 | 269015687.638842 INDIA | 1997 | 474432161.914014 INDIA | 1996 | 475389063.896689 INDIA | 1995 | 469635228.20625 INDIA | 1994 | 475833829.13089 INDIA | 1993 | 471083049.189837 INDIA | 1992 | 481790096.134844 INDONESIA | 1998 | 270396419.881559 INDONESIA | 1997 | 461549433.600579 INDONESIA | 1996 | 470060386.795302 INDONESIA | 1995 | 464949753.045557 INDONESIA | 1994 | 472083679.928837 INDONESIA | 1993 | 464903546.96132 INDONESIA | 1992 | 471626516.065173 IRAN | 1998 | 275930957.015346 IRAN | 1997 | 462223956.231692 IRAN | 1996 | 461669447.160106 IRAN | 1995 | 461660177.34987 IRAN | 1994 | 462325052.661309 IRAN | 1993 | 454257889.317383 IRAN | 1992 | 459581977.841749 IRAQ | 1998 | 272372664.656799 IRAQ | 1997 | 471883197.552789 IRAQ | 1996 | 469384399.214108 IRAQ | 1995 | 471199094.23552 IRAQ | 1994 | 474301964.597738 IRAQ | 1993 | 469737892.728182 IRAQ | 1992 | 473638706.123807 JAPAN | 1998 | 273670704.881546 JAPAN | 1997 | 470902520.270904 JAPAN | 1996 | 467149769.158512 JAPAN | 1995 | 468595355.870274 JAPAN | 1994 | 471411688.626504 JAPAN | 1993 | 472484846.092504 JAPAN | 1992 | 461858510.786252 JORDAN | 1998 | 273580174.452239 JORDAN | 1997 | 462933101.017266 JORDAN | 1996 | 459675105.178762 JORDAN | 1995 | 462247951.410172 JORDAN | 1994 | 466107154.695891 JORDAN | 1993 | 464964323.626154 JORDAN | 1992 | 454120044.748057 KENYA | 1998 | 280275929.991525 KENYA | 1997 | 473084399.554691 KENYA | 1996 | 477587972.408001 KENYA | 1995 | 468384490.196776 KENYA | 1994 | 474085941.726429 KENYA | 1993 | 478574790.266812 KENYA | 1992 | 483424126.000608 MOROCCO | 1998 | 269906877.477584 MOROCCO | 1997 | 467157004.268697 MOROCCO | 1996 | 467171047.232866 MOROCCO | 1995 | 476632133.969889 MOROCCO | 1994 | 472404788.891834 MOROCCO | 1993 | 464403783.253436 MOROCCO | 1992 | 465069481.608406 MOZAMBIQUE | 1998 | 271536317.376563 MOZAMBIQUE | 1997 | 457824305.601991 MOZAMBIQUE | 1996 | 459768818.081623 MOZAMBIQUE | 1995 | 458277502.578151 MOZAMBIQUE | 1994 | 457731968.156055 MOZAMBIQUE | 1993 | 455182417.345207 MOZAMBIQUE | 1992 | 456006552.388918 PERU | 1998 | 276878316.700551 PERU | 1997 | 465048717.49023 PERU | 1996 | 476031112.45411 PERU | 1995 | 466567222.801215 PERU | 1994 | 466943799.197332 PERU | 1993 | 470453226.735958 PERU | 1992 | 460027435.474122 ROMANIA | 1998 | 271397775.320884 ROMANIA | 1997 | 457669241.215955 ROMANIA | 1996 | 464567117.501742 ROMANIA | 1995 | 458938837.00208 ROMANIA | 1994 | 458386588.546198 ROMANIA | 1993 | 459662596.242585 ROMANIA | 1992 | 469585971.602621 RUSSIA | 1998 | 276585682.914083 RUSSIA | 1997 | 475728452.798671 RUSSIA | 1996 | 469538534.402368 RUSSIA | 1995 | 472544446.095467 RUSSIA | 1994 | 476344017.362833 RUSSIA | 1993 | 473453694.671977 RUSSIA | 1992 | 475677963.295345 SAUDI ARABIA | 1998 | 278381960.0468 SAUDI ARABIA | 1997 | 471862799.612321 SAUDI ARABIA | 1996 | 479949208.553212 SAUDI ARABIA | 1995 | 473905268.262346 SAUDI ARABIA | 1994 | 472579124.778197 SAUDI ARABIA | 1993 | 476296189.592768 SAUDI ARABIA | 1992 | 475916507.380822 UNITED KINGDOM | 1998 | 272310895.430959 UNITED KINGDOM | 1997 | 458631674.106498 UNITED KINGDOM | 1996 | 465218666.399007 UNITED KINGDOM | 1995 | 468267634.50915 UNITED KINGDOM | 1994 | 456238751.678363 UNITED KINGDOM | 1993 | 461227998.035264 UNITED KINGDOM | 1992 | 463883326.698832 UNITED STATES | 1998 | 282260456.408845 UNITED STATES | 1997 | 468938678.71098 UNITED STATES | 1996 | 476735950.282235 UNITED STATES | 1995 | 479506865.541336 UNITED STATES | 1994 | 475349513.071479 UNITED STATES | 1993 | 475359792.096903 UNITED STATES | 1992 | 478673910.532169 VIETNAM | 1998 | 270534573.206965 VIETNAM | 1997 | 466759714.947259 VIETNAM | 1996 | 463126712.875099 VIETNAM | 1995 | 455101232.582661 VIETNAM | 1994 | 458145540.191673 VIETNAM | 1993 | 467702486.548513 VIETNAM | 1992 | 465620586.698904 (175 rows) COMMIT; COMMIT