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 | 266848711.045678 ALGERIA | 1997 | 446008016.570925 ALGERIA | 1996 | 463328293.362138 ALGERIA | 1995 | 459696224.423487 ALGERIA | 1994 | 456959347.943522 ALGERIA | 1993 | 463226108.606874 ALGERIA | 1992 | 460518498.362292 ARGENTINA | 1998 | 269550879.668789 ARGENTINA | 1997 | 467551203.152382 ARGENTINA | 1996 | 466601272.10795 ARGENTINA | 1995 | 460987938.947631 ARGENTINA | 1994 | 465712482.438107 ARGENTINA | 1993 | 465356616.086143 ARGENTINA | 1992 | 455992022.14163 BRAZIL | 1998 | 273301740.20321 BRAZIL | 1997 | 482041747.774781 BRAZIL | 1996 | 473007669.692355 BRAZIL | 1995 | 467448166.876148 BRAZIL | 1994 | 469079157.134964 BRAZIL | 1993 | 482868938.701692 BRAZIL | 1992 | 473447662.416739 CANADA | 1998 | 271021712.220697 CANADA | 1997 | 468238108.690629 CANADA | 1996 | 475390199.315736 CANADA | 1995 | 473073787.362483 CANADA | 1994 | 482707808.885479 CANADA | 1993 | 478838492.490956 CANADA | 1992 | 472495356.459897 CHINA | 1998 | 273378153.631982 CHINA | 1997 | 465709551.247726 CHINA | 1996 | 475663440.39776 CHINA | 1995 | 472563605.867612 CHINA | 1994 | 475088059.584392 CHINA | 1993 | 467073845.453178 CHINA | 1992 | 468366978.357801 EGYPT | 1998 | 273847399.243481 EGYPT | 1997 | 464645150.150573 EGYPT | 1996 | 471139279.587738 EGYPT | 1995 | 462842823.944674 EGYPT | 1994 | 455061170.711965 EGYPT | 1993 | 461078406.062544 EGYPT | 1992 | 465338737.890363 ETHIOPIA | 1998 | 268568418.566452 ETHIOPIA | 1997 | 456593250.207076 ETHIOPIA | 1996 | 465430600.349421 ETHIOPIA | 1995 | 465652171.954002 ETHIOPIA | 1994 | 463650267.663114 ETHIOPIA | 1993 | 456196482.617925 ETHIOPIA | 1992 | 467179403.620866 FRANCE | 1998 | 270826112.422725 FRANCE | 1997 | 463541844.630617 FRANCE | 1996 | 466451057.185963 FRANCE | 1995 | 469869823.562414 FRANCE | 1994 | 464606685.458074 FRANCE | 1993 | 465281067.322239 FRANCE | 1992 | 463410525.281146 GERMANY | 1998 | 272437423.312397 GERMANY | 1997 | 462967468.675443 GERMANY | 1996 | 464809474.987682 GERMANY | 1995 | 468787203.263599 GERMANY | 1994 | 468927075.561878 GERMANY | 1993 | 468412041.362009 GERMANY | 1992 | 472477265.292128 INDIA | 1998 | 270788941.736754 INDIA | 1997 | 467788178.900795 INDIA | 1996 | 477101474.520725 INDIA | 1995 | 476303350.469244 INDIA | 1994 | 466458134.963728 INDIA | 1993 | 470057094.218613 INDIA | 1992 | 478333375.768411 INDONESIA | 1998 | 271332337.026466 INDONESIA | 1997 | 467995170.975257 INDONESIA | 1996 | 463784251.938096 INDONESIA | 1995 | 461820858.710133 INDONESIA | 1994 | 459275604.38486 INDONESIA | 1993 | 465213024.951359 INDONESIA | 1992 | 468343291.180066 IRAN | 1998 | 274236710.888675 IRAN | 1997 | 468579297.566288 IRAN | 1996 | 463875715.163321 IRAN | 1995 | 468882283.865828 IRAN | 1994 | 464424086.464844 IRAN | 1993 | 462990216.308862 IRAN | 1992 | 461331289.887293 IRAQ | 1998 | 271478462.032632 IRAQ | 1997 | 468306537.119571 IRAQ | 1996 | 481687106.606962 IRAQ | 1995 | 458975483.066959 IRAQ | 1994 | 470361734.565303 IRAQ | 1993 | 473572111.437517 IRAQ | 1992 | 466367505.089181 JAPAN | 1998 | 274232523.272211 JAPAN | 1997 | 469719655.658296 JAPAN | 1996 | 471131889.267238 JAPAN | 1995 | 466242414.803255 JAPAN | 1994 | 458378063.223495 JAPAN | 1993 | 467794109.877261 JAPAN | 1992 | 461297579.88782 JORDAN | 1998 | 269464789.164304 JORDAN | 1997 | 469308012.451919 JORDAN | 1996 | 462783404.52851 JORDAN | 1995 | 462234228.71479 JORDAN | 1994 | 457903700.903627 JORDAN | 1993 | 471474627.537771 JORDAN | 1992 | 462876913.296242 KENYA | 1998 | 280879782.576348 KENYA | 1997 | 473340275.469689 KENYA | 1996 | 474832312.557619 KENYA | 1995 | 477115916.040301 KENYA | 1994 | 480154164.311509 KENYA | 1993 | 474267547.172001 KENYA | 1992 | 477546573.953261 MOROCCO | 1998 | 276454598.39162 MOROCCO | 1997 | 471056966.630585 MOROCCO | 1996 | 462482000.974126 MOROCCO | 1995 | 464036831.209754 MOROCCO | 1994 | 470575602.090306 MOROCCO | 1993 | 462961637.266812 MOROCCO | 1992 | 464550369.626857 MOZAMBIQUE | 1998 | 265840845.472378 MOZAMBIQUE | 1997 | 454820364.654887 MOZAMBIQUE | 1996 | 453788732.751592 MOZAMBIQUE | 1995 | 448923787.704247 MOZAMBIQUE | 1994 | 450804998.061563 MOZAMBIQUE | 1993 | 463287922.302683 MOZAMBIQUE | 1992 | 458948421.393471 PERU | 1998 | 271458218.03603 PERU | 1997 | 460562825.059525 PERU | 1996 | 458941980.183322 PERU | 1995 | 454640952.639339 PERU | 1994 | 455487624.362693 PERU | 1993 | 463407901.301571 PERU | 1992 | 462823091.817225 ROMANIA | 1998 | 275223267.168908 ROMANIA | 1997 | 456283528.083274 ROMANIA | 1996 | 463822536.842609 ROMANIA | 1995 | 467666513.051174 ROMANIA | 1994 | 468846283.355609 ROMANIA | 1993 | 459572752.812681 ROMANIA | 1992 | 455926959.740059 RUSSIA | 1998 | 277173046.95394 RUSSIA | 1997 | 475753700.991193 RUSSIA | 1996 | 471030225.558684 RUSSIA | 1995 | 477374551.447885 RUSSIA | 1994 | 471532446.091315 RUSSIA | 1993 | 462884228.349319 RUSSIA | 1992 | 467236867.592429 SAUDI ARABIA | 1998 | 272956459.72431 SAUDI ARABIA | 1997 | 467917509.155171 SAUDI ARABIA | 1996 | 468084066.962849 SAUDI ARABIA | 1995 | 468654705.494728 SAUDI ARABIA | 1994 | 462304727.076209 SAUDI ARABIA | 1993 | 464755577.468206 SAUDI ARABIA | 1992 | 470930954.245946 UNITED KINGDOM | 1998 | 267631955.383817 UNITED KINGDOM | 1997 | 465400012.708428 UNITED KINGDOM | 1996 | 459312725.237134 UNITED KINGDOM | 1995 | 462239322.339996 UNITED KINGDOM | 1994 | 454898276.386172 UNITED KINGDOM | 1993 | 460932296.00996 UNITED KINGDOM | 1992 | 464050103.292564 UNITED STATES | 1998 | 276464722.741155 UNITED STATES | 1997 | 481330303.522848 UNITED STATES | 1996 | 473233859.790881 UNITED STATES | 1995 | 472317490.541776 UNITED STATES | 1994 | 472767918.15629 UNITED STATES | 1993 | 473088761.13883 UNITED STATES | 1992 | 471250249.581064 VIETNAM | 1998 | 274666252.31752 VIETNAM | 1997 | 467226261.161012 VIETNAM | 1996 | 480894497.617124 VIETNAM | 1995 | 466644382.310556 VIETNAM | 1994 | 475627267.557541 VIETNAM | 1993 | 476595357.602043 VIETNAM | 1992 | 471049741.328483 (175 rows) COMMIT; COMMIT