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 '%olive%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 260488482.060749 ALGERIA | 1997 | 448699956.163804 ALGERIA | 1996 | 451382001.177414 ALGERIA | 1995 | 449124629.947091 ALGERIA | 1994 | 449198386.582023 ALGERIA | 1993 | 455254927.940728 ALGERIA | 1992 | 458449266.779285 ARGENTINA | 1998 | 275680138.495643 ARGENTINA | 1997 | 478365797.007196 ARGENTINA | 1996 | 473025805.830929 ARGENTINA | 1995 | 471349101.568963 ARGENTINA | 1994 | 470307262.947329 ARGENTINA | 1993 | 464806235.238081 ARGENTINA | 1992 | 477978885.619247 BRAZIL | 1998 | 274154949.705744 BRAZIL | 1997 | 465121603.750735 BRAZIL | 1996 | 468970057.250075 BRAZIL | 1995 | 470382547.711811 BRAZIL | 1994 | 468030554.380292 BRAZIL | 1993 | 467169166.002829 BRAZIL | 1992 | 468305005.323394 CANADA | 1998 | 279005889.24885 CANADA | 1997 | 474738319.778324 CANADA | 1996 | 466338030.511497 CANADA | 1995 | 474937666.195068 CANADA | 1994 | 478253984.07719 CANADA | 1993 | 471410480.000773 CANADA | 1992 | 483355230.463028 CHINA | 1998 | 273105562.950717 CHINA | 1997 | 466597920.528995 CHINA | 1996 | 466932804.987762 CHINA | 1995 | 466741703.251212 CHINA | 1994 | 475441849.159126 CHINA | 1993 | 467257680.57714 CHINA | 1992 | 463988927.456947 EGYPT | 1998 | 272527589.622982 EGYPT | 1997 | 459469385.572267 EGYPT | 1996 | 468276764.059292 EGYPT | 1995 | 460214630.950546 EGYPT | 1994 | 462819446.312783 EGYPT | 1993 | 460767313.596963 EGYPT | 1992 | 462491267.081222 ETHIOPIA | 1998 | 268242263.119303 ETHIOPIA | 1997 | 455683759.278541 ETHIOPIA | 1996 | 458920498.839344 ETHIOPIA | 1995 | 464221231.782656 ETHIOPIA | 1994 | 453449362.323354 ETHIOPIA | 1993 | 458949759.653965 ETHIOPIA | 1992 | 459928441.426622 FRANCE | 1998 | 268959165.753064 FRANCE | 1997 | 455697177.46029 FRANCE | 1996 | 459965658.461809 FRANCE | 1995 | 458591093.100261 FRANCE | 1994 | 464149311.883258 FRANCE | 1993 | 456545288.722141 FRANCE | 1992 | 456561005.383254 GERMANY | 1998 | 284084855.972721 GERMANY | 1997 | 478597228.771528 GERMANY | 1996 | 476651492.378409 GERMANY | 1995 | 477314327.106544 GERMANY | 1994 | 471549880.681869 GERMANY | 1993 | 481343487.02933 GERMANY | 1992 | 474088302.102794 INDIA | 1998 | 269409933.002733 INDIA | 1997 | 469710481.297067 INDIA | 1996 | 468023207.913996 INDIA | 1995 | 467288515.763039 INDIA | 1994 | 468074713.345956 INDIA | 1993 | 473982318.553646 INDIA | 1992 | 468998867.433329 INDONESIA | 1998 | 271409700.690087 INDONESIA | 1997 | 460164368.587591 INDONESIA | 1996 | 461956998.410337 INDONESIA | 1995 | 458229624.997773 INDONESIA | 1994 | 467616638.656396 INDONESIA | 1993 | 469579515.456472 INDONESIA | 1992 | 467320733.797709 IRAN | 1998 | 263116799.464131 IRAN | 1997 | 450996294.309349 IRAN | 1996 | 450912806.626946 IRAN | 1995 | 454033119.360566 IRAN | 1994 | 454018505.67098 IRAN | 1993 | 458450126.038303 IRAN | 1992 | 455864720.512664 IRAQ | 1998 | 272093091.024818 IRAQ | 1997 | 468433074.880468 IRAQ | 1996 | 470251296.820919 IRAQ | 1995 | 460262969.775018 IRAQ | 1994 | 477561401.815208 IRAQ | 1993 | 466671472.055239 IRAQ | 1992 | 475841112.616162 JAPAN | 1998 | 271253068.815348 JAPAN | 1997 | 463521785.026591 JAPAN | 1996 | 455854160.45162 JAPAN | 1995 | 466429789.028504 JAPAN | 1994 | 459738747.182751 JAPAN | 1993 | 461099041.691615 JAPAN | 1992 | 462170271.388461 JORDAN | 1998 | 268635357.288286 JORDAN | 1997 | 449828263.951884 JORDAN | 1996 | 452622424.018843 JORDAN | 1995 | 450585787.890841 JORDAN | 1994 | 457062416.445165 JORDAN | 1993 | 451097225.913079 JORDAN | 1992 | 456395268.926677 KENYA | 1998 | 264019624.643337 KENYA | 1997 | 467874001.445734 KENYA | 1996 | 456593886.409498 KENYA | 1995 | 464858602.567294 KENYA | 1994 | 471692690.140887 KENYA | 1993 | 463443736.208147 KENYA | 1992 | 462944704.085502 MOROCCO | 1998 | 274682666.887117 MOROCCO | 1997 | 460139861.69361 MOROCCO | 1996 | 454724893.55245 MOROCCO | 1995 | 470648984.581744 MOROCCO | 1994 | 463997503.484339 MOROCCO | 1993 | 468132048.897735 MOROCCO | 1992 | 464851095.821671 MOZAMBIQUE | 1998 | 270053069.280024 MOZAMBIQUE | 1997 | 454686286.939094 MOZAMBIQUE | 1996 | 465730079.52718 MOZAMBIQUE | 1995 | 464780020.568132 MOZAMBIQUE | 1994 | 465839036.682014 MOZAMBIQUE | 1993 | 442681400.493732 MOZAMBIQUE | 1992 | 459639936.227435 PERU | 1998 | 276933835.030269 PERU | 1997 | 462931553.097003 PERU | 1996 | 464937607.543855 PERU | 1995 | 465100703.187815 PERU | 1994 | 473076374.255875 PERU | 1993 | 470240330.684544 PERU | 1992 | 471154856.802419 ROMANIA | 1998 | 272771797.524707 ROMANIA | 1997 | 462490317.16373 ROMANIA | 1996 | 465583617.089633 ROMANIA | 1995 | 463888459.629475 ROMANIA | 1994 | 462711202.07893 ROMANIA | 1993 | 452504276.353035 ROMANIA | 1992 | 466849810.05222 RUSSIA | 1998 | 272238482.937056 RUSSIA | 1997 | 467661330.321391 RUSSIA | 1996 | 472406619.945008 RUSSIA | 1995 | 467663435.829974 RUSSIA | 1994 | 470946607.298804 RUSSIA | 1993 | 466096021.809574 RUSSIA | 1992 | 479054572.881898 SAUDI ARABIA | 1998 | 277653776.865004 SAUDI ARABIA | 1997 | 470524407.297258 SAUDI ARABIA | 1996 | 478912339.187954 SAUDI ARABIA | 1995 | 468604213.421264 SAUDI ARABIA | 1994 | 480338185.413428 SAUDI ARABIA | 1993 | 468434729.556151 SAUDI ARABIA | 1992 | 473804637.06603 UNITED KINGDOM | 1998 | 269577884.403705 UNITED KINGDOM | 1997 | 463959097.710373 UNITED KINGDOM | 1996 | 455815552.650461 UNITED KINGDOM | 1995 | 464398093.921859 UNITED KINGDOM | 1994 | 460623644.379417 UNITED KINGDOM | 1993 | 461917188.230145 UNITED KINGDOM | 1992 | 459783613.284606 UNITED STATES | 1998 | 275551147.628507 UNITED STATES | 1997 | 480533875.951857 UNITED STATES | 1996 | 463328624.513748 UNITED STATES | 1995 | 474093839.236754 UNITED STATES | 1994 | 474726255.147831 UNITED STATES | 1993 | 470401599.113156 UNITED STATES | 1992 | 471560725.483344 VIETNAM | 1998 | 268294186.25046 VIETNAM | 1997 | 460203455.598153 VIETNAM | 1996 | 460404752.219898 VIETNAM | 1995 | 459834815.026024 VIETNAM | 1994 | 464339534.45303 VIETNAM | 1993 | 470051590.486843 VIETNAM | 1992 | 467372739.129224 (175 rows) COMMIT; COMMIT