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 | 271338754.770941 ALGERIA | 1997 | 461263870.349059 ALGERIA | 1996 | 463564107.922193 ALGERIA | 1995 | 464474802.778322 ALGERIA | 1994 | 456750676.462069 ALGERIA | 1993 | 460199340.009722 ALGERIA | 1992 | 460145390.681606 ARGENTINA | 1998 | 274317351.475009 ARGENTINA | 1997 | 464612880.157811 ARGENTINA | 1996 | 466659949.137837 ARGENTINA | 1995 | 464639307.983179 ARGENTINA | 1994 | 477273584.927673 ARGENTINA | 1993 | 469612121.527171 ARGENTINA | 1992 | 467147892.837223 BRAZIL | 1998 | 271981954.492347 BRAZIL | 1997 | 466527195.714537 BRAZIL | 1996 | 465420672.166786 BRAZIL | 1995 | 460936366.302509 BRAZIL | 1994 | 462441174.254836 BRAZIL | 1993 | 466642643.120589 BRAZIL | 1992 | 470414562.931462 CANADA | 1998 | 275386595.174018 CANADA | 1997 | 472496904.758596 CANADA | 1996 | 478020009.799434 CANADA | 1995 | 471936920.963726 CANADA | 1994 | 479151000.42479 CANADA | 1993 | 476347561.723487 CANADA | 1992 | 473408066.717105 CHINA | 1998 | 274952756.079129 CHINA | 1997 | 467266537.490783 CHINA | 1996 | 464189343.932681 CHINA | 1995 | 464492885.979205 CHINA | 1994 | 471613326.32497 CHINA | 1993 | 468690952.095299 CHINA | 1992 | 467467025.960916 EGYPT | 1998 | 266034267.234439 EGYPT | 1997 | 456400190.325864 EGYPT | 1996 | 456952668.75262 EGYPT | 1995 | 451870681.164575 EGYPT | 1994 | 449340760.736368 EGYPT | 1993 | 457052200.241094 EGYPT | 1992 | 460487144.196365 ETHIOPIA | 1998 | 269023312.173548 ETHIOPIA | 1997 | 451671828.084859 ETHIOPIA | 1996 | 455711862.412722 ETHIOPIA | 1995 | 455485234.299674 ETHIOPIA | 1994 | 455309974.386669 ETHIOPIA | 1993 | 457355367.679435 ETHIOPIA | 1992 | 455430599.328033 FRANCE | 1998 | 266087586.258431 FRANCE | 1997 | 460706852.05448 FRANCE | 1996 | 464445451.244398 FRANCE | 1995 | 466997931.833119 FRANCE | 1994 | 464050018.828364 FRANCE | 1993 | 464324011.310668 FRANCE | 1992 | 467305604.593892 GERMANY | 1998 | 283609247.684035 GERMANY | 1997 | 471499388.68212 GERMANY | 1996 | 473861449.096783 GERMANY | 1995 | 480759553.300093 GERMANY | 1994 | 470253092.454164 GERMANY | 1993 | 484565805.611833 GERMANY | 1992 | 476051875.730162 INDIA | 1998 | 272935310.357588 INDIA | 1997 | 471872383.025606 INDIA | 1996 | 479606910.942014 INDIA | 1995 | 461756979.843953 INDIA | 1994 | 467900928.272636 INDIA | 1993 | 467435094.68292 INDIA | 1992 | 466390354.59182 INDONESIA | 1998 | 269950305.177879 INDONESIA | 1997 | 460220977.126344 INDONESIA | 1996 | 468174260.955365 INDONESIA | 1995 | 449468956.933271 INDONESIA | 1994 | 461186913.865238 INDONESIA | 1993 | 463967396.607862 INDONESIA | 1992 | 462719371.139517 IRAN | 1998 | 275587704.020711 IRAN | 1997 | 463062724.863346 IRAN | 1996 | 468093939.229953 IRAN | 1995 | 468218245.522561 IRAN | 1994 | 463044661.243632 IRAN | 1993 | 469479490.977245 IRAN | 1992 | 475089505.653715 IRAQ | 1998 | 278769062.442517 IRAQ | 1997 | 477601727.304069 IRAQ | 1996 | 480764539.768214 IRAQ | 1995 | 475262093.42932 IRAQ | 1994 | 480882120.222219 IRAQ | 1993 | 471639123.07329 IRAQ | 1992 | 485544672.547721 JAPAN | 1998 | 268151799.144745 JAPAN | 1997 | 459919353.093509 JAPAN | 1996 | 462377175.280631 JAPAN | 1995 | 458230028.01897 JAPAN | 1994 | 459181072.005683 JAPAN | 1993 | 459419582.797943 JAPAN | 1992 | 461662826.299429 JORDAN | 1998 | 268312161.44374 JORDAN | 1997 | 456367223.883091 JORDAN | 1996 | 452924746.581058 JORDAN | 1995 | 459369659.030823 JORDAN | 1994 | 455645104.514387 JORDAN | 1993 | 454252210.309295 JORDAN | 1992 | 458053737.307382 KENYA | 1998 | 267978155.920533 KENYA | 1997 | 467636231.41162 KENYA | 1996 | 465494963.114212 KENYA | 1995 | 466968325.357137 KENYA | 1994 | 470806139.5753 KENYA | 1993 | 470755005.417623 KENYA | 1992 | 471549364.121807 MOROCCO | 1998 | 273435405.93448 MOROCCO | 1997 | 464876923.728793 MOROCCO | 1996 | 467888877.532547 MOROCCO | 1995 | 463055288.907595 MOROCCO | 1994 | 468788315.808426 MOROCCO | 1993 | 467934118.253323 MOROCCO | 1992 | 464907247.113525 MOZAMBIQUE | 1998 | 272178703.130262 MOZAMBIQUE | 1997 | 461831417.552916 MOZAMBIQUE | 1996 | 461903958.563623 MOZAMBIQUE | 1995 | 466026811.901414 MOZAMBIQUE | 1994 | 454325847.179735 MOZAMBIQUE | 1993 | 461139594.970941 MOZAMBIQUE | 1992 | 461124033.348725 PERU | 1998 | 267854021.916029 PERU | 1997 | 456812589.233353 PERU | 1996 | 458556861.336275 PERU | 1995 | 469570796.54489 PERU | 1994 | 456148975.302954 PERU | 1993 | 455532761.959507 PERU | 1992 | 462181285.131289 ROMANIA | 1998 | 273599403.02142 ROMANIA | 1997 | 477774631.909868 ROMANIA | 1996 | 476862805.862248 ROMANIA | 1995 | 474781417.353903 ROMANIA | 1994 | 473455647.225469 ROMANIA | 1993 | 479801960.679649 ROMANIA | 1992 | 471136979.652962 RUSSIA | 1998 | 279295704.555284 RUSSIA | 1997 | 471542531.729518 RUSSIA | 1996 | 481168424.715958 RUSSIA | 1995 | 483389229.232687 RUSSIA | 1994 | 480041564.611396 RUSSIA | 1993 | 471120250.905898 RUSSIA | 1992 | 476563640.586536 SAUDI ARABIA | 1998 | 276806150.173926 SAUDI ARABIA | 1997 | 463701758.938844 SAUDI ARABIA | 1996 | 471963640.537413 SAUDI ARABIA | 1995 | 470372890.636028 SAUDI ARABIA | 1994 | 471468935.484547 SAUDI ARABIA | 1993 | 472137702.872177 SAUDI ARABIA | 1992 | 473841221.112785 UNITED KINGDOM | 1998 | 268710326.322359 UNITED KINGDOM | 1997 | 458018042.202876 UNITED KINGDOM | 1996 | 456090747.81272 UNITED KINGDOM | 1995 | 456498152.67098 UNITED KINGDOM | 1994 | 452089347.120656 UNITED KINGDOM | 1993 | 452599237.816961 UNITED KINGDOM | 1992 | 454651437.353753 UNITED STATES | 1998 | 282575386.571913 UNITED STATES | 1997 | 472277604.935207 UNITED STATES | 1996 | 478370393.768359 UNITED STATES | 1995 | 471501374.802887 UNITED STATES | 1994 | 476518148.492553 UNITED STATES | 1993 | 480149794.253526 UNITED STATES | 1992 | 475948120.768004 VIETNAM | 1998 | 270981918.773421 VIETNAM | 1997 | 467251568.526836 VIETNAM | 1996 | 467668594.50479 VIETNAM | 1995 | 465530831.594478 VIETNAM | 1994 | 464581891.300984 VIETNAM | 1993 | 462447229.162811 VIETNAM | 1992 | 455611102.234527 (175 rows) COMMIT; COMMIT