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 '%navy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268110455.661231 ALGERIA | 1997 | 460116473.659424 ALGERIA | 1996 | 459066854.439628 ALGERIA | 1995 | 465029265.707926 ALGERIA | 1994 | 453590291.045396 ALGERIA | 1993 | 450376790.075348 ALGERIA | 1992 | 457307633.190044 ARGENTINA | 1998 | 278695799.998282 ARGENTINA | 1997 | 478560489.683354 ARGENTINA | 1996 | 483143224.760982 ARGENTINA | 1995 | 473665539.630523 ARGENTINA | 1994 | 478321299.315728 ARGENTINA | 1993 | 471723195.809546 ARGENTINA | 1992 | 468842233.368293 BRAZIL | 1998 | 269194246.008225 BRAZIL | 1997 | 465635103.734932 BRAZIL | 1996 | 464275868.375553 BRAZIL | 1995 | 464697704.49002 BRAZIL | 1994 | 460870422.715797 BRAZIL | 1993 | 462603702.331108 BRAZIL | 1992 | 461971259.619319 CANADA | 1998 | 276005012.674 CANADA | 1997 | 472794502.578084 CANADA | 1996 | 467193159.262569 CANADA | 1995 | 468992853.783756 CANADA | 1994 | 478292444.378559 CANADA | 1993 | 475215487.482485 CANADA | 1992 | 473293425.824005 CHINA | 1998 | 265638475.414528 CHINA | 1997 | 458652425.834115 CHINA | 1996 | 448623178.416059 CHINA | 1995 | 459987727.192073 CHINA | 1994 | 459954449.165143 CHINA | 1993 | 459020484.749045 CHINA | 1992 | 462257811.479435 EGYPT | 1998 | 271961564.552004 EGYPT | 1997 | 463813193.340995 EGYPT | 1996 | 459285126.670106 EGYPT | 1995 | 464563669.403786 EGYPT | 1994 | 467076739.285153 EGYPT | 1993 | 468610420.382438 EGYPT | 1992 | 465079819.047942 ETHIOPIA | 1998 | 267921861.314342 ETHIOPIA | 1997 | 451405395.864817 ETHIOPIA | 1996 | 448885865.781327 ETHIOPIA | 1995 | 453303349.197129 ETHIOPIA | 1994 | 455048323.140608 ETHIOPIA | 1993 | 455031913.885099 ETHIOPIA | 1992 | 454795836.524044 FRANCE | 1998 | 264643911.210285 FRANCE | 1997 | 457001750.154377 FRANCE | 1996 | 456146320.897286 FRANCE | 1995 | 457314187.954838 FRANCE | 1994 | 450538228.343452 FRANCE | 1993 | 452441663.564675 FRANCE | 1992 | 454939429.992981 GERMANY | 1998 | 276106670.727726 GERMANY | 1997 | 464230998.707381 GERMANY | 1996 | 472127850.29156 GERMANY | 1995 | 476710570.600284 GERMANY | 1994 | 477404112.233075 GERMANY | 1993 | 473194957.816539 GERMANY | 1992 | 474553883.549059 INDIA | 1998 | 276098932.048936 INDIA | 1997 | 478440540.867126 INDIA | 1996 | 472380918.516111 INDIA | 1995 | 470401906.897833 INDIA | 1994 | 478914473.240284 INDIA | 1993 | 478689361.517794 INDIA | 1992 | 474861339.674407 INDONESIA | 1998 | 265300427.604914 INDONESIA | 1997 | 454313168.486842 INDONESIA | 1996 | 455742136.815952 INDONESIA | 1995 | 450103941.831552 INDONESIA | 1994 | 460625933.844031 INDONESIA | 1993 | 455839588.840373 INDONESIA | 1992 | 449971386.854539 IRAN | 1998 | 266630341.429138 IRAN | 1997 | 465790545.122375 IRAN | 1996 | 463590832.461748 IRAN | 1995 | 461395087.30169 IRAN | 1994 | 464028773.784639 IRAN | 1993 | 464742140.672932 IRAN | 1992 | 462338751.152407 IRAQ | 1998 | 271164749.897624 IRAQ | 1997 | 463471863.214923 IRAQ | 1996 | 465420473.91664 IRAQ | 1995 | 462144502.827142 IRAQ | 1994 | 463751343.542024 IRAQ | 1993 | 467635105.567639 IRAQ | 1992 | 460850628.930513 JAPAN | 1998 | 268109122.00877 JAPAN | 1997 | 463646297.778187 JAPAN | 1996 | 462957714.735634 JAPAN | 1995 | 458172925.472787 JAPAN | 1994 | 459006836.212591 JAPAN | 1993 | 464010088.247962 JAPAN | 1992 | 459837231.179588 JORDAN | 1998 | 271977746.696554 JORDAN | 1997 | 459885752.391922 JORDAN | 1996 | 456394676.435085 JORDAN | 1995 | 459219093.021838 JORDAN | 1994 | 451574169.975726 JORDAN | 1993 | 463791537.762912 JORDAN | 1992 | 460002304.743707 KENYA | 1998 | 279013110.798117 KENYA | 1997 | 475968411.277668 KENYA | 1996 | 475005521.913435 KENYA | 1995 | 477645180.22922 KENYA | 1994 | 467754629.269923 KENYA | 1993 | 473075783.786596 KENYA | 1992 | 462393736.41425 MOROCCO | 1998 | 272830147.932726 MOROCCO | 1997 | 470926673.905608 MOROCCO | 1996 | 464763111.68537 MOROCCO | 1995 | 467090509.343688 MOROCCO | 1994 | 470742578.392471 MOROCCO | 1993 | 455895315.16289 MOROCCO | 1992 | 467994049.538644 MOZAMBIQUE | 1998 | 271355663.061194 MOZAMBIQUE | 1997 | 457598066.362988 MOZAMBIQUE | 1996 | 461301446.384418 MOZAMBIQUE | 1995 | 458930564.172178 MOZAMBIQUE | 1994 | 468543847.719951 MOZAMBIQUE | 1993 | 464286068.027776 MOZAMBIQUE | 1992 | 466448289.422295 PERU | 1998 | 273130646.397112 PERU | 1997 | 467336514.326477 PERU | 1996 | 461607489.6378 PERU | 1995 | 453707278.916073 PERU | 1994 | 466093225.885544 PERU | 1993 | 464044599.471675 PERU | 1992 | 472852433.480956 ROMANIA | 1998 | 272011545.850616 ROMANIA | 1997 | 460790333.381844 ROMANIA | 1996 | 470455694.883585 ROMANIA | 1995 | 470337707.235687 ROMANIA | 1994 | 465633604.940607 ROMANIA | 1993 | 457393833.286617 ROMANIA | 1992 | 467950276.294218 RUSSIA | 1998 | 277244222.982059 RUSSIA | 1997 | 479023427.59702 RUSSIA | 1996 | 477857213.124678 RUSSIA | 1995 | 468145405.836077 RUSSIA | 1994 | 467890938.404091 RUSSIA | 1993 | 475835158.660684 RUSSIA | 1992 | 469670555.489204 SAUDI ARABIA | 1998 | 279610073.238062 SAUDI ARABIA | 1997 | 465135283.335442 SAUDI ARABIA | 1996 | 465595676.208845 SAUDI ARABIA | 1995 | 479221146.472265 SAUDI ARABIA | 1994 | 471911360.923721 SAUDI ARABIA | 1993 | 462373593.998675 SAUDI ARABIA | 1992 | 470284804.862218 UNITED KINGDOM | 1998 | 272105413.220692 UNITED KINGDOM | 1997 | 464538526.797284 UNITED KINGDOM | 1996 | 467337481.318415 UNITED KINGDOM | 1995 | 459275387.958782 UNITED KINGDOM | 1994 | 459884306.164632 UNITED KINGDOM | 1993 | 468882516.613619 UNITED KINGDOM | 1992 | 461657846.042695 UNITED STATES | 1998 | 281390850.392679 UNITED STATES | 1997 | 471740165.941298 UNITED STATES | 1996 | 471346794.480181 UNITED STATES | 1995 | 470879256.956744 UNITED STATES | 1994 | 472976878.004115 UNITED STATES | 1993 | 460580254.791052 UNITED STATES | 1992 | 472467561.866335 VIETNAM | 1998 | 272684605.807911 VIETNAM | 1997 | 454456527.98552 VIETNAM | 1996 | 455242870.519925 VIETNAM | 1995 | 462884537.737022 VIETNAM | 1994 | 456503876.857073 VIETNAM | 1993 | 458188904.816627 VIETNAM | 1992 | 459593012.898518 (175 rows) COMMIT; COMMIT