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 '%navajo%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272564693.078113 ALGERIA | 1997 | 458218503.512047 ALGERIA | 1996 | 460297762.194778 ALGERIA | 1995 | 463743828.860093 ALGERIA | 1994 | 452144061.113209 ALGERIA | 1993 | 464661947.475922 ALGERIA | 1992 | 463215111.074533 ARGENTINA | 1998 | 281273339.284601 ARGENTINA | 1997 | 473932951.79227 ARGENTINA | 1996 | 475245272.791206 ARGENTINA | 1995 | 472779939.581301 ARGENTINA | 1994 | 476758975.281943 ARGENTINA | 1993 | 476953158.241854 ARGENTINA | 1992 | 474890190.873296 BRAZIL | 1998 | 265890234.907824 BRAZIL | 1997 | 461090098.587686 BRAZIL | 1996 | 464193425.854002 BRAZIL | 1995 | 467696327.902908 BRAZIL | 1994 | 469810535.138788 BRAZIL | 1993 | 470136721.728864 BRAZIL | 1992 | 468152265.933906 CANADA | 1998 | 284773553.861969 CANADA | 1997 | 489032000.507356 CANADA | 1996 | 482629010.979635 CANADA | 1995 | 491059294.15011 CANADA | 1994 | 487827709.910393 CANADA | 1993 | 485709178.286953 CANADA | 1992 | 491675887.741116 CHINA | 1998 | 282422479.213166 CHINA | 1997 | 471005127.180807 CHINA | 1996 | 467373052.133033 CHINA | 1995 | 470763255.65797 CHINA | 1994 | 474759623.508621 CHINA | 1993 | 469787943.915214 CHINA | 1992 | 476389612.049956 EGYPT | 1998 | 271514898.129372 EGYPT | 1997 | 462311148.716466 EGYPT | 1996 | 469033587.511386 EGYPT | 1995 | 461592934.335952 EGYPT | 1994 | 466738890.545949 EGYPT | 1993 | 465282764.465036 EGYPT | 1992 | 464056693.841818 ETHIOPIA | 1998 | 277727575.546764 ETHIOPIA | 1997 | 459570072.15525 ETHIOPIA | 1996 | 458673434.736763 ETHIOPIA | 1995 | 459564440.781745 ETHIOPIA | 1994 | 462840259.99275 ETHIOPIA | 1993 | 469830000.008069 ETHIOPIA | 1992 | 460320546.928931 FRANCE | 1998 | 267842794.432377 FRANCE | 1997 | 456967987.635736 FRANCE | 1996 | 461630266.557578 FRANCE | 1995 | 456028777.582439 FRANCE | 1994 | 461100343.266009 FRANCE | 1993 | 459599487.140639 FRANCE | 1992 | 450593360.631614 GERMANY | 1998 | 279451365.013339 GERMANY | 1997 | 469167404.194434 GERMANY | 1996 | 464287145.709654 GERMANY | 1995 | 468021290.229599 GERMANY | 1994 | 469249509.53435 GERMANY | 1993 | 467462682.820464 GERMANY | 1992 | 470384891.576051 INDIA | 1998 | 278406969.09543 INDIA | 1997 | 483497527.147136 INDIA | 1996 | 486607772.611587 INDIA | 1995 | 478340841.384085 INDIA | 1994 | 481333278.653628 INDIA | 1993 | 486422911.076597 INDIA | 1992 | 480272061.94945 INDONESIA | 1998 | 270887170.630467 INDONESIA | 1997 | 459945657.2924 INDONESIA | 1996 | 474126643.956332 INDONESIA | 1995 | 464638996.584992 INDONESIA | 1994 | 459666206.147177 INDONESIA | 1993 | 469616872.757343 INDONESIA | 1992 | 465550312.420651 IRAN | 1998 | 272740320.294454 IRAN | 1997 | 466172231.458922 IRAN | 1996 | 470578560.673357 IRAN | 1995 | 468058029.345782 IRAN | 1994 | 465611271.025643 IRAN | 1993 | 471834140.283287 IRAN | 1992 | 474670384.420069 IRAQ | 1998 | 279917098.566549 IRAQ | 1997 | 480804175.406139 IRAQ | 1996 | 479031183.814733 IRAQ | 1995 | 479231526.547673 IRAQ | 1994 | 482868349.732783 IRAQ | 1993 | 471961577.660923 IRAQ | 1992 | 480220590.547652 JAPAN | 1998 | 276305246.296394 JAPAN | 1997 | 477350127.575176 JAPAN | 1996 | 476462906.423859 JAPAN | 1995 | 471543896.374239 JAPAN | 1994 | 468205207.756616 JAPAN | 1993 | 469521823.907129 JAPAN | 1992 | 472002863.264298 JORDAN | 1998 | 272156395.364772 JORDAN | 1997 | 465176177.063985 JORDAN | 1996 | 465756929.967907 JORDAN | 1995 | 463180711.006625 JORDAN | 1994 | 457164848.132001 JORDAN | 1993 | 464419855.025834 JORDAN | 1992 | 473790819.636912 KENYA | 1998 | 280014349.451872 KENYA | 1997 | 475981150.029997 KENYA | 1996 | 472356674.024231 KENYA | 1995 | 468682498.170084 KENYA | 1994 | 471823068.008958 KENYA | 1993 | 471339299.255014 KENYA | 1992 | 467701421.856592 MOROCCO | 1998 | 277515171.608075 MOROCCO | 1997 | 473573140.557118 MOROCCO | 1996 | 482098231.090383 MOROCCO | 1995 | 473662666.369536 MOROCCO | 1994 | 470398608.912018 MOROCCO | 1993 | 471329650.154775 MOROCCO | 1992 | 477702901.876736 MOZAMBIQUE | 1998 | 271311164.803607 MOZAMBIQUE | 1997 | 458234685.39051 MOZAMBIQUE | 1996 | 465225412.723444 MOZAMBIQUE | 1995 | 471279812.947179 MOZAMBIQUE | 1994 | 460947024.296095 MOZAMBIQUE | 1993 | 462409846.493104 MOZAMBIQUE | 1992 | 461451094.548951 PERU | 1998 | 281399414.739794 PERU | 1997 | 471824508.336945 PERU | 1996 | 471962781.5355 PERU | 1995 | 475539085.049767 PERU | 1994 | 471047996.763873 PERU | 1993 | 468782981.194308 PERU | 1992 | 473151094.719278 ROMANIA | 1998 | 282745413.118954 ROMANIA | 1997 | 472114749.625487 ROMANIA | 1996 | 478984664.888108 ROMANIA | 1995 | 474256240.774774 ROMANIA | 1994 | 477363851.04996 ROMANIA | 1993 | 475710259.553674 ROMANIA | 1992 | 475115015.910012 RUSSIA | 1998 | 278720395.806498 RUSSIA | 1997 | 478269465.42295 RUSSIA | 1996 | 482964140.310812 RUSSIA | 1995 | 486556822.303822 RUSSIA | 1994 | 483087849.376356 RUSSIA | 1993 | 479542012.824921 RUSSIA | 1992 | 482118080.588578 SAUDI ARABIA | 1998 | 282080557.090219 SAUDI ARABIA | 1997 | 465984645.533174 SAUDI ARABIA | 1996 | 474079583.008968 SAUDI ARABIA | 1995 | 471860891.303787 SAUDI ARABIA | 1994 | 471519358.706809 SAUDI ARABIA | 1993 | 464886392.414151 SAUDI ARABIA | 1992 | 462380147.483196 UNITED KINGDOM | 1998 | 264912016.778282 UNITED KINGDOM | 1997 | 466787365.669858 UNITED KINGDOM | 1996 | 456413459.00121 UNITED KINGDOM | 1995 | 461153519.9694 UNITED KINGDOM | 1994 | 455969836.69812 UNITED KINGDOM | 1993 | 451225167.944091 UNITED KINGDOM | 1992 | 454575993.10949 UNITED STATES | 1998 | 279859063.956321 UNITED STATES | 1997 | 473805922.425789 UNITED STATES | 1996 | 479032951.688651 UNITED STATES | 1995 | 472723942.02242 UNITED STATES | 1994 | 479441222.89782 UNITED STATES | 1993 | 475974711.413974 UNITED STATES | 1992 | 475876187.45059 VIETNAM | 1998 | 266555188.349646 VIETNAM | 1997 | 458632122.061264 VIETNAM | 1996 | 473757766.040289 VIETNAM | 1995 | 465713145.647379 VIETNAM | 1994 | 468827704.369588 VIETNAM | 1993 | 463045352.859251 VIETNAM | 1992 | 460668290.954484 (175 rows) COMMIT; COMMIT