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 '%antique%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 266186376.131742 ALGERIA | 1997 | 447540799.989273 ALGERIA | 1996 | 453488072.488777 ALGERIA | 1995 | 456286735.771143 ALGERIA | 1994 | 443716846.528277 ALGERIA | 1993 | 452076208.492808 ALGERIA | 1992 | 450884269.095941 ARGENTINA | 1998 | 278869345.850058 ARGENTINA | 1997 | 472777294.561562 ARGENTINA | 1996 | 468560672.006132 ARGENTINA | 1995 | 474229911.525559 ARGENTINA | 1994 | 471717385.105537 ARGENTINA | 1993 | 467688808.845211 ARGENTINA | 1992 | 472411359.850063 BRAZIL | 1998 | 270249831.530298 BRAZIL | 1997 | 464102037.051658 BRAZIL | 1996 | 459207186.742435 BRAZIL | 1995 | 464549639.698124 BRAZIL | 1994 | 460623255.024733 BRAZIL | 1993 | 459654540.053047 BRAZIL | 1992 | 458859828.478162 CANADA | 1998 | 276741224.542566 CANADA | 1997 | 473684535.946261 CANADA | 1996 | 468811250.894968 CANADA | 1995 | 468376249.795466 CANADA | 1994 | 480161113.363966 CANADA | 1993 | 478568011.035595 CANADA | 1992 | 464618435.055679 CHINA | 1998 | 268544764.036417 CHINA | 1997 | 456369095.813862 CHINA | 1996 | 460525737.63954 CHINA | 1995 | 458178382.963796 CHINA | 1994 | 455358233.200572 CHINA | 1993 | 469626399.575814 CHINA | 1992 | 458717799.625581 EGYPT | 1998 | 274355632.813872 EGYPT | 1997 | 469597595.503014 EGYPT | 1996 | 471615097.458848 EGYPT | 1995 | 470552547.001083 EGYPT | 1994 | 468088291.613336 EGYPT | 1993 | 468580431.969882 EGYPT | 1992 | 459274242.85367 ETHIOPIA | 1998 | 264429800.073971 ETHIOPIA | 1997 | 455700330.230762 ETHIOPIA | 1996 | 459451931.601695 ETHIOPIA | 1995 | 456135471.585025 ETHIOPIA | 1994 | 464553554.086525 ETHIOPIA | 1993 | 461561019.48967 ETHIOPIA | 1992 | 455721475.338545 FRANCE | 1998 | 266459791.630125 FRANCE | 1997 | 456292449.460289 FRANCE | 1996 | 455857002.539767 FRANCE | 1995 | 453052938.697182 FRANCE | 1994 | 453789907.097075 FRANCE | 1993 | 453105977.328303 FRANCE | 1992 | 455123911.554243 GERMANY | 1998 | 284708165.467405 GERMANY | 1997 | 471929421.984816 GERMANY | 1996 | 473535667.515587 GERMANY | 1995 | 482028783.18555 GERMANY | 1994 | 470724974.408794 GERMANY | 1993 | 469446061.221097 GERMANY | 1992 | 467728079.697109 INDIA | 1998 | 271597236.558138 INDIA | 1997 | 466336378.978405 INDIA | 1996 | 480460104.915407 INDIA | 1995 | 472231469.022623 INDIA | 1994 | 472010393.671122 INDIA | 1993 | 469370258.419648 INDIA | 1992 | 471788474.632128 INDONESIA | 1998 | 273346492.122011 INDONESIA | 1997 | 463422229.385467 INDONESIA | 1996 | 463169207.10432 INDONESIA | 1995 | 462242796.25956 INDONESIA | 1994 | 469454562.701353 INDONESIA | 1993 | 463711187.835722 INDONESIA | 1992 | 469346929.402074 IRAN | 1998 | 275700459.084973 IRAN | 1997 | 464573525.828999 IRAN | 1996 | 476942912.768148 IRAN | 1995 | 456437900.18144 IRAN | 1994 | 462717341.471857 IRAN | 1993 | 469468216.540761 IRAN | 1992 | 470256970.931147 IRAQ | 1998 | 267192203.968694 IRAQ | 1997 | 456568816.631656 IRAQ | 1996 | 465171601.071279 IRAQ | 1995 | 451996609.676073 IRAQ | 1994 | 454221901.716127 IRAQ | 1993 | 456837381.979903 IRAQ | 1992 | 455237849.24374 JAPAN | 1998 | 272359212.208915 JAPAN | 1997 | 465001506.925473 JAPAN | 1996 | 461569879.939206 JAPAN | 1995 | 467633217.140149 JAPAN | 1994 | 464311024.234505 JAPAN | 1993 | 464645008.003292 JAPAN | 1992 | 459492214.332629 JORDAN | 1998 | 262344484.221021 JORDAN | 1997 | 450921548.174527 JORDAN | 1996 | 448462986.383122 JORDAN | 1995 | 453788536.123881 JORDAN | 1994 | 448712847.560995 JORDAN | 1993 | 454855532.072852 JORDAN | 1992 | 446845990.950717 KENYA | 1998 | 274980022.998609 KENYA | 1997 | 474608240.405613 KENYA | 1996 | 479033551.88708 KENYA | 1995 | 465770187.102226 KENYA | 1994 | 477985822.607823 KENYA | 1993 | 469663424.941144 KENYA | 1992 | 475094662.542771 MOROCCO | 1998 | 270081720.065797 MOROCCO | 1997 | 471484792.528501 MOROCCO | 1996 | 468875661.77253 MOROCCO | 1995 | 463581898.514268 MOROCCO | 1994 | 464511880.932074 MOROCCO | 1993 | 463887387.581797 MOROCCO | 1992 | 473912437.435749 MOZAMBIQUE | 1998 | 268353167.440604 MOZAMBIQUE | 1997 | 458775100.57121 MOZAMBIQUE | 1996 | 458024330.935162 MOZAMBIQUE | 1995 | 458773190.852532 MOZAMBIQUE | 1994 | 452592248.469623 MOZAMBIQUE | 1993 | 456450263.368209 MOZAMBIQUE | 1992 | 456990567.83711 PERU | 1998 | 269454466.341077 PERU | 1997 | 450847694.631654 PERU | 1996 | 455929319.891018 PERU | 1995 | 453351133.510628 PERU | 1994 | 450929397.967513 PERU | 1993 | 454782007.555234 PERU | 1992 | 459373152.038233 ROMANIA | 1998 | 271665107.824769 ROMANIA | 1997 | 469597335.145194 ROMANIA | 1996 | 466938687.035609 ROMANIA | 1995 | 467017022.634351 ROMANIA | 1994 | 462549939.011455 ROMANIA | 1993 | 463205609.711248 ROMANIA | 1992 | 459593474.505406 RUSSIA | 1998 | 272856255.862681 RUSSIA | 1997 | 468253289.074501 RUSSIA | 1996 | 474176740.987432 RUSSIA | 1995 | 465654946.16392 RUSSIA | 1994 | 462620188.385287 RUSSIA | 1993 | 466179427.017468 RUSSIA | 1992 | 467502325.700577 SAUDI ARABIA | 1998 | 271587031.419084 SAUDI ARABIA | 1997 | 464365256.021515 SAUDI ARABIA | 1996 | 470724685.955583 SAUDI ARABIA | 1995 | 470756449.835836 SAUDI ARABIA | 1994 | 470693944.536298 SAUDI ARABIA | 1993 | 466496301.013091 SAUDI ARABIA | 1992 | 471220415.108797 UNITED KINGDOM | 1998 | 274505212.760721 UNITED KINGDOM | 1997 | 460358922.938464 UNITED KINGDOM | 1996 | 459544035.036688 UNITED KINGDOM | 1995 | 459877530.883818 UNITED KINGDOM | 1994 | 454535416.166926 UNITED KINGDOM | 1993 | 463486984.76152 UNITED KINGDOM | 1992 | 461108926.889032 UNITED STATES | 1998 | 266683379.119828 UNITED STATES | 1997 | 461343910.937866 UNITED STATES | 1996 | 467079955.960613 UNITED STATES | 1995 | 470195650.346834 UNITED STATES | 1994 | 469618163.086599 UNITED STATES | 1993 | 463303093.650728 UNITED STATES | 1992 | 456859038.264158 VIETNAM | 1998 | 270872611.643193 VIETNAM | 1997 | 462657849.434968 VIETNAM | 1996 | 460866350.625242 VIETNAM | 1995 | 459969964.278995 VIETNAM | 1994 | 466944856.390651 VIETNAM | 1993 | 464932437.858621 VIETNAM | 1992 | 464069798.384759 (175 rows) COMMIT; COMMIT