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 '%snow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268375005.811859 ALGERIA | 1997 | 446981081.793372 ALGERIA | 1996 | 468272473.015629 ALGERIA | 1995 | 463839382.602107 ALGERIA | 1994 | 461880324.820135 ALGERIA | 1993 | 462562907.577126 ALGERIA | 1992 | 462166907.943697 ARGENTINA | 1998 | 271265072.886244 ARGENTINA | 1997 | 467013523.124392 ARGENTINA | 1996 | 463142893.409058 ARGENTINA | 1995 | 458388537.760283 ARGENTINA | 1994 | 474972322.747034 ARGENTINA | 1993 | 458410313.325314 ARGENTINA | 1992 | 464932007.05972 BRAZIL | 1998 | 272674694.741007 BRAZIL | 1997 | 461697257.349768 BRAZIL | 1996 | 464396629.373818 BRAZIL | 1995 | 461967802.381467 BRAZIL | 1994 | 468843334.729243 BRAZIL | 1993 | 462999463.002875 BRAZIL | 1992 | 462789307.133071 CANADA | 1998 | 282108266.74904 CANADA | 1997 | 484456940.046978 CANADA | 1996 | 489626197.286935 CANADA | 1995 | 486852277.288989 CANADA | 1994 | 485541814.333412 CANADA | 1993 | 480688050.315818 CANADA | 1992 | 485651199.707749 CHINA | 1998 | 274455656.598475 CHINA | 1997 | 468708587.163181 CHINA | 1996 | 462367298.753493 CHINA | 1995 | 460834202.550092 CHINA | 1994 | 466758415.421544 CHINA | 1993 | 462919756.24882 CHINA | 1992 | 465390979.391004 EGYPT | 1998 | 271612501.631619 EGYPT | 1997 | 462545351.312625 EGYPT | 1996 | 460300722.233405 EGYPT | 1995 | 466415311.468897 EGYPT | 1994 | 467973524.687625 EGYPT | 1993 | 465975601.152008 EGYPT | 1992 | 461602060.483373 ETHIOPIA | 1998 | 271147098.110209 ETHIOPIA | 1997 | 473570730.068632 ETHIOPIA | 1996 | 456108970.940345 ETHIOPIA | 1995 | 467144107.322593 ETHIOPIA | 1994 | 472419248.800208 ETHIOPIA | 1993 | 457314063.230069 ETHIOPIA | 1992 | 456781185.503849 FRANCE | 1998 | 270297408.935636 FRANCE | 1997 | 460553001.38767 FRANCE | 1996 | 460547744.047801 FRANCE | 1995 | 462652966.339444 FRANCE | 1994 | 463385690.9483 FRANCE | 1993 | 464657612.148109 FRANCE | 1992 | 462883969.499386 GERMANY | 1998 | 278830510.664336 GERMANY | 1997 | 478290878.065437 GERMANY | 1996 | 463995644.755794 GERMANY | 1995 | 478144960.799713 GERMANY | 1994 | 473702221.509436 GERMANY | 1993 | 474939163.66358 GERMANY | 1992 | 475434830.989736 INDIA | 1998 | 271874482.332312 INDIA | 1997 | 475099716.746244 INDIA | 1996 | 476145458.378477 INDIA | 1995 | 462107185.952602 INDIA | 1994 | 474723397.175004 INDIA | 1993 | 469863376.165128 INDIA | 1992 | 469014422.313245 INDONESIA | 1998 | 268318585.824244 INDONESIA | 1997 | 458621087.503583 INDONESIA | 1996 | 467029085.147935 INDONESIA | 1995 | 452885867.694944 INDONESIA | 1994 | 456482426.293154 INDONESIA | 1993 | 465331088.800161 INDONESIA | 1992 | 458201305.311475 IRAN | 1998 | 276018835.298509 IRAN | 1997 | 463904359.107456 IRAN | 1996 | 475613968.575358 IRAN | 1995 | 455573154.616059 IRAN | 1994 | 463671448.396432 IRAN | 1993 | 465197128.334537 IRAN | 1992 | 468254758.291343 IRAQ | 1998 | 288277256.957142 IRAQ | 1997 | 472966708.368236 IRAQ | 1996 | 485433280.57399 IRAQ | 1995 | 476985526.736258 IRAQ | 1994 | 482561084.654817 IRAQ | 1993 | 472696213.122482 IRAQ | 1992 | 480585431.828591 JAPAN | 1998 | 271859924.587949 JAPAN | 1997 | 466872864.778054 JAPAN | 1996 | 465532727.457287 JAPAN | 1995 | 470324311.772363 JAPAN | 1994 | 464396238.337645 JAPAN | 1993 | 458464357.415328 JAPAN | 1992 | 467576094.189439 JORDAN | 1998 | 268729752.424365 JORDAN | 1997 | 457292846.615168 JORDAN | 1996 | 461969926.154036 JORDAN | 1995 | 453010982.732024 JORDAN | 1994 | 459228973.293146 JORDAN | 1993 | 454220345.734424 JORDAN | 1992 | 460354403.110409 KENYA | 1998 | 280490069.41154 KENYA | 1997 | 468091410.898723 KENYA | 1996 | 479521850.484098 KENYA | 1995 | 471487713.833543 KENYA | 1994 | 477955739.968812 KENYA | 1993 | 469262576.69008 KENYA | 1992 | 474533977.097342 MOROCCO | 1998 | 269451393.784574 MOROCCO | 1997 | 466065849.997231 MOROCCO | 1996 | 464875442.12524 MOROCCO | 1995 | 462502038.249072 MOROCCO | 1994 | 471129502.6613 MOROCCO | 1993 | 465155362.942317 MOROCCO | 1992 | 460374053.382938 MOZAMBIQUE | 1998 | 266942635.800826 MOZAMBIQUE | 1997 | 458317279.152805 MOZAMBIQUE | 1996 | 457899135.211883 MOZAMBIQUE | 1995 | 456762389.870874 MOZAMBIQUE | 1994 | 459253524.556969 MOZAMBIQUE | 1993 | 464775759.638387 MOZAMBIQUE | 1992 | 455023464.821348 PERU | 1998 | 274314360.738086 PERU | 1997 | 466390800.068093 PERU | 1996 | 467091426.726919 PERU | 1995 | 481390037.953518 PERU | 1994 | 469229851.635621 PERU | 1993 | 464076877.387081 PERU | 1992 | 470316732.101649 ROMANIA | 1998 | 270947173.91668 ROMANIA | 1997 | 465784258.570778 ROMANIA | 1996 | 474068421.40897 ROMANIA | 1995 | 473527083.889935 ROMANIA | 1994 | 466550583.714041 ROMANIA | 1993 | 468883754.752709 ROMANIA | 1992 | 464312062.225433 RUSSIA | 1998 | 283787951.812186 RUSSIA | 1997 | 479563147.695214 RUSSIA | 1996 | 479179458.097579 RUSSIA | 1995 | 465724770.607203 RUSSIA | 1994 | 476101037.501428 RUSSIA | 1993 | 469587972.282562 RUSSIA | 1992 | 470511508.679531 SAUDI ARABIA | 1998 | 275129389.19576 SAUDI ARABIA | 1997 | 465165103.197357 SAUDI ARABIA | 1996 | 468288474.008232 SAUDI ARABIA | 1995 | 467367644.255087 SAUDI ARABIA | 1994 | 467529001.43074 SAUDI ARABIA | 1993 | 459504614.202856 SAUDI ARABIA | 1992 | 469705971.738023 UNITED KINGDOM | 1998 | 267578371.088662 UNITED KINGDOM | 1997 | 463011497.586952 UNITED KINGDOM | 1996 | 457986906.607318 UNITED KINGDOM | 1995 | 456945504.819061 UNITED KINGDOM | 1994 | 456985370.89932 UNITED KINGDOM | 1993 | 465494057.340257 UNITED KINGDOM | 1992 | 453591437.386898 UNITED STATES | 1998 | 271472847.347569 UNITED STATES | 1997 | 470560152.016195 UNITED STATES | 1996 | 461308570.636087 UNITED STATES | 1995 | 466647878.590675 UNITED STATES | 1994 | 461336511.969262 UNITED STATES | 1993 | 470837898.439724 UNITED STATES | 1992 | 468105511.36714 VIETNAM | 1998 | 275108546.146277 VIETNAM | 1997 | 464401735.130271 VIETNAM | 1996 | 471221687.669358 VIETNAM | 1995 | 465729322.468928 VIETNAM | 1994 | 469616969.028903 VIETNAM | 1993 | 458507357.756922 VIETNAM | 1992 | 460606304.833756 (175 rows) COMMIT; COMMIT