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 '%orange%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271002361.828493 ALGERIA | 1997 | 460447284.149752 ALGERIA | 1996 | 462882423.062279 ALGERIA | 1995 | 463622086.644365 ALGERIA | 1994 | 456477767.645156 ALGERIA | 1993 | 459524971.029323 ALGERIA | 1992 | 458900063.825192 ARGENTINA | 1998 | 273997708.621557 ARGENTINA | 1997 | 463449670.879298 ARGENTINA | 1996 | 465973993.045183 ARGENTINA | 1995 | 463048213.65805 ARGENTINA | 1994 | 476540157.285368 ARGENTINA | 1993 | 468401169.090592 ARGENTINA | 1992 | 467487740.871783 BRAZIL | 1998 | 271730500.005493 BRAZIL | 1997 | 466175886.823926 BRAZIL | 1996 | 463661644.441937 BRAZIL | 1995 | 461053160.872812 BRAZIL | 1994 | 460769982.499563 BRAZIL | 1993 | 466378673.732433 BRAZIL | 1992 | 469899020.98024 CANADA | 1998 | 274823152.674235 CANADA | 1997 | 473289369.544848 CANADA | 1996 | 477281339.002592 CANADA | 1995 | 472378929.334982 CANADA | 1994 | 478870104.20631 CANADA | 1993 | 476284421.398013 CANADA | 1992 | 472487476.231513 CHINA | 1998 | 275305192.059226 CHINA | 1997 | 466817775.50999 CHINA | 1996 | 463729636.192276 CHINA | 1995 | 464099009.412156 CHINA | 1994 | 470312505.52751 CHINA | 1993 | 467324238.553851 CHINA | 1992 | 466653562.612183 EGYPT | 1998 | 266276807.069541 EGYPT | 1997 | 456366518.562634 EGYPT | 1996 | 456232341.880794 EGYPT | 1995 | 451042971.120239 EGYPT | 1994 | 448289396.168489 EGYPT | 1993 | 456867512.081911 EGYPT | 1992 | 459296388.273705 ETHIOPIA | 1998 | 269026606.179273 ETHIOPIA | 1997 | 451597254.274522 ETHIOPIA | 1996 | 454859011.533593 ETHIOPIA | 1995 | 454961308.170246 ETHIOPIA | 1994 | 454547842.675958 ETHIOPIA | 1993 | 456052771.252893 ETHIOPIA | 1992 | 454470981.396468 FRANCE | 1998 | 266103124.528309 FRANCE | 1997 | 460287330.766306 FRANCE | 1996 | 463080980.403081 FRANCE | 1995 | 467443692.637596 FRANCE | 1994 | 463537033.449977 FRANCE | 1993 | 464183570.034506 FRANCE | 1992 | 466607603.658763 GERMANY | 1998 | 282909061.911897 GERMANY | 1997 | 470431290.96418 GERMANY | 1996 | 473546086.962209 GERMANY | 1995 | 480626545.801792 GERMANY | 1994 | 469567773.119982 GERMANY | 1993 | 484038286.474805 GERMANY | 1992 | 476288619.486334 INDIA | 1998 | 272973473.623915 INDIA | 1997 | 471892136.35801 INDIA | 1996 | 478721377.094252 INDIA | 1995 | 460617723.432894 INDIA | 1994 | 467546701.722044 INDIA | 1993 | 467127365.976224 INDIA | 1992 | 465391306.895181 INDONESIA | 1998 | 269394157.345958 INDONESIA | 1997 | 460231396.498575 INDONESIA | 1996 | 467665312.415215 INDONESIA | 1995 | 448425611.439626 INDONESIA | 1994 | 460394963.179431 INDONESIA | 1993 | 463130001.427309 INDONESIA | 1992 | 463469290.874556 IRAN | 1998 | 274671018.637186 IRAN | 1997 | 462851959.255516 IRAN | 1996 | 467905718.12668 IRAN | 1995 | 467462749.002258 IRAN | 1994 | 462107841.677215 IRAN | 1993 | 469253953.707909 IRAN | 1992 | 473850610.711154 IRAQ | 1998 | 279329618.902179 IRAQ | 1997 | 476564070.889225 IRAQ | 1996 | 480002142.092227 IRAQ | 1995 | 475261887.677656 IRAQ | 1994 | 480492759.393994 IRAQ | 1993 | 470433978.765077 IRAQ | 1992 | 484603851.51227 JAPAN | 1998 | 268046071.119159 JAPAN | 1997 | 459722746.408254 JAPAN | 1996 | 462038825.607492 JAPAN | 1995 | 457718189.49401 JAPAN | 1994 | 458393430.978634 JAPAN | 1993 | 458719912.154183 JAPAN | 1992 | 460815557.274894 JORDAN | 1998 | 268748638.034819 JORDAN | 1997 | 455999453.978681 JORDAN | 1996 | 451641773.042771 JORDAN | 1995 | 459252886.360267 JORDAN | 1994 | 455091491.739217 JORDAN | 1993 | 454736280.265495 JORDAN | 1992 | 458490114.154152 KENYA | 1998 | 268533424.547951 KENYA | 1997 | 466301467.80906 KENYA | 1996 | 465371248.458274 KENYA | 1995 | 467219569.448247 KENYA | 1994 | 470392168.44566 KENYA | 1993 | 469845557.752115 KENYA | 1992 | 471714996.988447 MOROCCO | 1998 | 272361009.406611 MOROCCO | 1997 | 463986142.32625 MOROCCO | 1996 | 468207311.471241 MOROCCO | 1995 | 462397143.167633 MOROCCO | 1994 | 467694506.774715 MOROCCO | 1993 | 468291995.414078 MOROCCO | 1992 | 465108004.932054 MOZAMBIQUE | 1998 | 271887767.451381 MOZAMBIQUE | 1997 | 462340578.485416 MOZAMBIQUE | 1996 | 461584138.35065 MOZAMBIQUE | 1995 | 465727014.687445 MOZAMBIQUE | 1994 | 453958252.514799 MOZAMBIQUE | 1993 | 460493836.174422 MOZAMBIQUE | 1992 | 460747937.73251 PERU | 1998 | 267845976.521813 PERU | 1997 | 455860602.93942 PERU | 1996 | 457454874.254344 PERU | 1995 | 469659448.921765 PERU | 1994 | 455340537.591889 PERU | 1993 | 455474003.661684 PERU | 1992 | 461608835.998862 ROMANIA | 1998 | 273086453.278355 ROMANIA | 1997 | 477111956.044191 ROMANIA | 1996 | 475943986.491349 ROMANIA | 1995 | 473503556.203828 ROMANIA | 1994 | 472263765.470213 ROMANIA | 1993 | 478565456.30332 ROMANIA | 1992 | 470158843.273853 RUSSIA | 1998 | 279770126.605244 RUSSIA | 1997 | 471080836.988205 RUSSIA | 1996 | 480198943.754328 RUSSIA | 1995 | 482920119.110825 RUSSIA | 1994 | 478922624.116519 RUSSIA | 1993 | 470106632.103325 RUSSIA | 1992 | 475936157.605089 SAUDI ARABIA | 1998 | 276556828.947054 SAUDI ARABIA | 1997 | 463991340.128143 SAUDI ARABIA | 1996 | 471952432.603333 SAUDI ARABIA | 1995 | 469941842.035625 SAUDI ARABIA | 1994 | 471248475.471175 SAUDI ARABIA | 1993 | 470943815.837144 SAUDI ARABIA | 1992 | 473455804.196705 UNITED KINGDOM | 1998 | 267843106.121762 UNITED KINGDOM | 1997 | 457250964.418756 UNITED KINGDOM | 1996 | 456294896.459126 UNITED KINGDOM | 1995 | 457062170.425157 UNITED KINGDOM | 1994 | 451549931.66352 UNITED KINGDOM | 1993 | 451946818.398978 UNITED KINGDOM | 1992 | 453613671.957586 UNITED STATES | 1998 | 282350394.67481 UNITED STATES | 1997 | 471290471.470492 UNITED STATES | 1996 | 477594695.251957 UNITED STATES | 1995 | 470700623.602877 UNITED STATES | 1994 | 476039161.112585 UNITED STATES | 1993 | 479955514.181879 UNITED STATES | 1992 | 475238004.973773 VIETNAM | 1998 | 270935268.661535 VIETNAM | 1997 | 466727800.401691 VIETNAM | 1996 | 467797642.412812 VIETNAM | 1995 | 464710613.71491 VIETNAM | 1994 | 464636184.14584 VIETNAM | 1993 | 462395817.878187 VIETNAM | 1992 | 455385644.44331 (175 rows) COMMIT; COMMIT