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 '%ghost%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272330681.345049 ALGERIA | 1997 | 459894978.34374 ALGERIA | 1996 | 466986382.845622 ALGERIA | 1995 | 466119805.724197 ALGERIA | 1994 | 457283125.345702 ALGERIA | 1993 | 470189710.960261 ALGERIA | 1992 | 464329429.281738 ARGENTINA | 1998 | 276073901.40283 ARGENTINA | 1997 | 471232215.801433 ARGENTINA | 1996 | 474947441.140884 ARGENTINA | 1995 | 471281607.509399 ARGENTINA | 1994 | 466256944.426598 ARGENTINA | 1993 | 466100062.022696 ARGENTINA | 1992 | 469088877.944019 BRAZIL | 1998 | 271594576.359721 BRAZIL | 1997 | 463861084.937524 BRAZIL | 1996 | 468233576.849127 BRAZIL | 1995 | 470295344.009441 BRAZIL | 1994 | 463408284.281695 BRAZIL | 1993 | 462523782.393133 BRAZIL | 1992 | 471586011.392546 CANADA | 1998 | 273836385.336172 CANADA | 1997 | 477996281.507248 CANADA | 1996 | 472168127.549516 CANADA | 1995 | 477619600.112977 CANADA | 1994 | 479873796.420799 CANADA | 1993 | 483038493.448066 CANADA | 1992 | 484524657.735498 CHINA | 1998 | 279757180.704235 CHINA | 1997 | 467864973.641833 CHINA | 1996 | 476749633.153304 CHINA | 1995 | 474082486.237433 CHINA | 1994 | 471000014.376093 CHINA | 1993 | 477503663.458494 CHINA | 1992 | 476031070.571137 EGYPT | 1998 | 275681186.98594 EGYPT | 1997 | 458289222.32129 EGYPT | 1996 | 464616557.361571 EGYPT | 1995 | 464916790.455164 EGYPT | 1994 | 462683944.580187 EGYPT | 1993 | 459980098.930967 EGYPT | 1992 | 468478333.81146 ETHIOPIA | 1998 | 266868755.745393 ETHIOPIA | 1997 | 453229827.350952 ETHIOPIA | 1996 | 458922213.399612 ETHIOPIA | 1995 | 460593627.384605 ETHIOPIA | 1994 | 456267046.575376 ETHIOPIA | 1993 | 456202159.358579 ETHIOPIA | 1992 | 457798692.164158 FRANCE | 1998 | 271376649.22383 FRANCE | 1997 | 459349382.529881 FRANCE | 1996 | 460339872.203879 FRANCE | 1995 | 464921375.989593 FRANCE | 1994 | 456150113.848884 FRANCE | 1993 | 465043354.383595 FRANCE | 1992 | 459481073.64977 GERMANY | 1998 | 277393117.551124 GERMANY | 1997 | 475737407.92105 GERMANY | 1996 | 468529507.86159 GERMANY | 1995 | 470407914.778489 GERMANY | 1994 | 473563089.969557 GERMANY | 1993 | 466275617.50608 GERMANY | 1992 | 461548761.376965 INDIA | 1998 | 270172877.752899 INDIA | 1997 | 468518545.54221 INDIA | 1996 | 476301043.391125 INDIA | 1995 | 466366753.504128 INDIA | 1994 | 463300611.91786 INDIA | 1993 | 463537689.031607 INDIA | 1992 | 466401294.937758 INDONESIA | 1998 | 273065105.298523 INDONESIA | 1997 | 456637047.289729 INDONESIA | 1996 | 459439665.713975 INDONESIA | 1995 | 464627518.139749 INDONESIA | 1994 | 454594400.839754 INDONESIA | 1993 | 460741066.516029 INDONESIA | 1992 | 468366951.049204 IRAN | 1998 | 272325843.760147 IRAN | 1997 | 461627330.217142 IRAN | 1996 | 461364008.450199 IRAN | 1995 | 460559294.902153 IRAN | 1994 | 467286852.610862 IRAN | 1993 | 462071698.415646 IRAN | 1992 | 468632895.756962 IRAQ | 1998 | 280186841.555871 IRAQ | 1997 | 472045190.059551 IRAQ | 1996 | 479936756.788458 IRAQ | 1995 | 476653231.132422 IRAQ | 1994 | 481367159.323931 IRAQ | 1993 | 466793543.56225 IRAQ | 1992 | 478100204.853495 JAPAN | 1998 | 274759864.181392 JAPAN | 1997 | 468088848.614982 JAPAN | 1996 | 457939292.584825 JAPAN | 1995 | 461099073.9816 JAPAN | 1994 | 462347875.565921 JAPAN | 1993 | 462308403.624564 JAPAN | 1992 | 462997947.524131 JORDAN | 1998 | 274203578.713183 JORDAN | 1997 | 468371179.105934 JORDAN | 1996 | 460048043.856261 JORDAN | 1995 | 464669328.609408 JORDAN | 1994 | 458160088.560113 JORDAN | 1993 | 460396570.479732 JORDAN | 1992 | 458763149.142548 KENYA | 1998 | 272549150.069509 KENYA | 1997 | 471150470.165681 KENYA | 1996 | 473889177.240947 KENYA | 1995 | 473180350.414033 KENYA | 1994 | 468642388.609749 KENYA | 1993 | 478976252.667502 KENYA | 1992 | 473194653.484141 MOROCCO | 1998 | 278115077.19412 MOROCCO | 1997 | 475424203.580746 MOROCCO | 1996 | 476997983.579193 MOROCCO | 1995 | 474688756.699929 MOROCCO | 1994 | 473566156.699136 MOROCCO | 1993 | 473025236.979936 MOROCCO | 1992 | 468551646.952746 MOZAMBIQUE | 1998 | 270745089.581991 MOZAMBIQUE | 1997 | 453420701.392934 MOZAMBIQUE | 1996 | 459063310.375823 MOZAMBIQUE | 1995 | 461877713.338786 MOZAMBIQUE | 1994 | 459089679.41113 MOZAMBIQUE | 1993 | 458496135.951512 MOZAMBIQUE | 1992 | 451108854.716773 PERU | 1998 | 275127180.468103 PERU | 1997 | 464354518.566449 PERU | 1996 | 467007659.150942 PERU | 1995 | 470246612.735235 PERU | 1994 | 460943472.786105 PERU | 1993 | 462827793.042636 PERU | 1992 | 466403419.466361 ROMANIA | 1998 | 276684362.142018 ROMANIA | 1997 | 469302427.420714 ROMANIA | 1996 | 470905527.672429 ROMANIA | 1995 | 475712507.753999 ROMANIA | 1994 | 469072826.936724 ROMANIA | 1993 | 472085538.80409 ROMANIA | 1992 | 474918024.833572 RUSSIA | 1998 | 274639224.204387 RUSSIA | 1997 | 470521143.293521 RUSSIA | 1996 | 473286381.127967 RUSSIA | 1995 | 462239290.770874 RUSSIA | 1994 | 475669433.740537 RUSSIA | 1993 | 470643341.277594 RUSSIA | 1992 | 471008586.297679 SAUDI ARABIA | 1998 | 271381171.103852 SAUDI ARABIA | 1997 | 483197102.579057 SAUDI ARABIA | 1996 | 472378239.376868 SAUDI ARABIA | 1995 | 474922793.193331 SAUDI ARABIA | 1994 | 473757479.439623 SAUDI ARABIA | 1993 | 464237309.344804 SAUDI ARABIA | 1992 | 471777936.549101 UNITED KINGDOM | 1998 | 267040035.830319 UNITED KINGDOM | 1997 | 458539029.973574 UNITED KINGDOM | 1996 | 462269112.370984 UNITED KINGDOM | 1995 | 449474015.645996 UNITED KINGDOM | 1994 | 456846427.685016 UNITED KINGDOM | 1993 | 447836697.926081 UNITED KINGDOM | 1992 | 460711386.103369 UNITED STATES | 1998 | 270724987.235782 UNITED STATES | 1997 | 464352032.785103 UNITED STATES | 1996 | 464084632.109499 UNITED STATES | 1995 | 460805321.500012 UNITED STATES | 1994 | 473231565.840932 UNITED STATES | 1993 | 471026545.551782 UNITED STATES | 1992 | 461508573.067255 VIETNAM | 1998 | 274691988.173806 VIETNAM | 1997 | 468406233.153298 VIETNAM | 1996 | 483862603.187566 VIETNAM | 1995 | 471474408.330386 VIETNAM | 1994 | 477789872.862139 VIETNAM | 1993 | 467840942.604572 VIETNAM | 1992 | 469760613.865176 (175 rows) COMMIT; COMMIT