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 '%ivory%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 262196920.640737 ALGERIA | 1997 | 458127802.602423 ALGERIA | 1996 | 460944610.833256 ALGERIA | 1995 | 461315375.61537 ALGERIA | 1994 | 459256796.278607 ALGERIA | 1993 | 453854892.004168 ALGERIA | 1992 | 468274745.816523 ARGENTINA | 1998 | 266543838.287615 ARGENTINA | 1997 | 454638957.791696 ARGENTINA | 1996 | 460293658.533387 ARGENTINA | 1995 | 453608879.349409 ARGENTINA | 1994 | 455088091.363049 ARGENTINA | 1993 | 458430432.430318 ARGENTINA | 1992 | 458499306.590288 BRAZIL | 1998 | 272279090.75948 BRAZIL | 1997 | 467844910.365831 BRAZIL | 1996 | 474414111.911258 BRAZIL | 1995 | 462686402.802809 BRAZIL | 1994 | 462309094.92115 BRAZIL | 1993 | 463614112.922597 BRAZIL | 1992 | 471564904.232149 CANADA | 1998 | 271270585.247659 CANADA | 1997 | 469820906.460554 CANADA | 1996 | 464551983.339002 CANADA | 1995 | 460855522.401247 CANADA | 1994 | 463685025.857414 CANADA | 1993 | 475819085.063309 CANADA | 1992 | 461054122.548554 CHINA | 1998 | 276894986.380849 CHINA | 1997 | 466381568.027153 CHINA | 1996 | 464769492.183944 CHINA | 1995 | 468131633.890563 CHINA | 1994 | 472674656.768898 CHINA | 1993 | 471776863.805291 CHINA | 1992 | 467218369.817508 EGYPT | 1998 | 277227267.202492 EGYPT | 1997 | 462358446.889555 EGYPT | 1996 | 462662549.794143 EGYPT | 1995 | 460677475.705207 EGYPT | 1994 | 458388793.619111 EGYPT | 1993 | 459152964.92138 EGYPT | 1992 | 466839250.388223 ETHIOPIA | 1998 | 275034762.462246 ETHIOPIA | 1997 | 459402719.43692 ETHIOPIA | 1996 | 463809750.086751 ETHIOPIA | 1995 | 457464975.904021 ETHIOPIA | 1994 | 463030822.691995 ETHIOPIA | 1993 | 456907784.921336 ETHIOPIA | 1992 | 456834416.411173 FRANCE | 1998 | 271867288.154999 FRANCE | 1997 | 455631604.006964 FRANCE | 1996 | 456247621.318564 FRANCE | 1995 | 459641725.610098 FRANCE | 1994 | 455505599.160059 FRANCE | 1993 | 455118085.165764 FRANCE | 1992 | 450046784.902454 GERMANY | 1998 | 275772037.795598 GERMANY | 1997 | 472549355.459328 GERMANY | 1996 | 471194517.441632 GERMANY | 1995 | 468443510.766779 GERMANY | 1994 | 467613407.442759 GERMANY | 1993 | 465431384.617549 GERMANY | 1992 | 469391909.288238 INDIA | 1998 | 279073536.182088 INDIA | 1997 | 477787230.95924 INDIA | 1996 | 473429508.037581 INDIA | 1995 | 473846060.451116 INDIA | 1994 | 474348047.593822 INDIA | 1993 | 476557295.114069 INDIA | 1992 | 482730578.24379 INDONESIA | 1998 | 265232938.948913 INDONESIA | 1997 | 454167331.161158 INDONESIA | 1996 | 463072560.947421 INDONESIA | 1995 | 454593448.943504 INDONESIA | 1994 | 457576477.004307 INDONESIA | 1993 | 462984173.418881 INDONESIA | 1992 | 461945318.523001 IRAN | 1998 | 267101952.249347 IRAN | 1997 | 466879544.303011 IRAN | 1996 | 468255713.604058 IRAN | 1995 | 464604604.588167 IRAN | 1994 | 460697015.285334 IRAN | 1993 | 463860356.575419 IRAN | 1992 | 464785825.858753 IRAQ | 1998 | 278755888.597074 IRAQ | 1997 | 469629920.008638 IRAQ | 1996 | 466278297.08755 IRAQ | 1995 | 463339166.866081 IRAQ | 1994 | 465564239.278166 IRAQ | 1993 | 466114564.975681 IRAQ | 1992 | 470550841.809707 JAPAN | 1998 | 269622725.614245 JAPAN | 1997 | 465211194.093858 JAPAN | 1996 | 469535560.691765 JAPAN | 1995 | 461867760.002078 JAPAN | 1994 | 467753703.005007 JAPAN | 1993 | 469549715.293587 JAPAN | 1992 | 460324078.458633 JORDAN | 1998 | 270726421.715889 JORDAN | 1997 | 459804243.287408 JORDAN | 1996 | 459309701.573182 JORDAN | 1995 | 461359059.441472 JORDAN | 1994 | 458910147.119966 JORDAN | 1993 | 456669936.735576 JORDAN | 1992 | 459262691.306185 KENYA | 1998 | 278998314.771105 KENYA | 1997 | 468252812.32528 KENYA | 1996 | 470961968.247742 KENYA | 1995 | 469422594.777721 KENYA | 1994 | 479986327.306486 KENYA | 1993 | 465724264.609634 KENYA | 1992 | 477270638.485147 MOROCCO | 1998 | 271146729.950711 MOROCCO | 1997 | 459056698.353859 MOROCCO | 1996 | 460958722.077833 MOROCCO | 1995 | 460467728.679782 MOROCCO | 1994 | 460318219.30361 MOROCCO | 1993 | 462312791.314894 MOROCCO | 1992 | 459545742.706732 MOZAMBIQUE | 1998 | 268843549.67724 MOZAMBIQUE | 1997 | 456608874.779755 MOZAMBIQUE | 1996 | 463337983.873553 MOZAMBIQUE | 1995 | 456236477.681114 MOZAMBIQUE | 1994 | 451331089.943859 MOZAMBIQUE | 1993 | 464671702.449849 MOZAMBIQUE | 1992 | 461092759.513438 PERU | 1998 | 275035936.336928 PERU | 1997 | 475934564.956153 PERU | 1996 | 472451459.354522 PERU | 1995 | 478328531.305787 PERU | 1994 | 467944771.825247 PERU | 1993 | 466774832.472976 PERU | 1992 | 468664702.420822 ROMANIA | 1998 | 271897514.083924 ROMANIA | 1997 | 459409702.985019 ROMANIA | 1996 | 471392570.621405 ROMANIA | 1995 | 466585172.907816 ROMANIA | 1994 | 464356052.874895 ROMANIA | 1993 | 456872057.185331 ROMANIA | 1992 | 465757259.344339 RUSSIA | 1998 | 274869884.618205 RUSSIA | 1997 | 467155709.872268 RUSSIA | 1996 | 473274453.422581 RUSSIA | 1995 | 468916895.573078 RUSSIA | 1994 | 466568794.796661 RUSSIA | 1993 | 474762854.50017 RUSSIA | 1992 | 465690254.51742 SAUDI ARABIA | 1998 | 265218434.681877 SAUDI ARABIA | 1997 | 469797213.542627 SAUDI ARABIA | 1996 | 467717439.639757 SAUDI ARABIA | 1995 | 465117163.198183 SAUDI ARABIA | 1994 | 471781643.916476 SAUDI ARABIA | 1993 | 464302723.797698 SAUDI ARABIA | 1992 | 476506073.488547 UNITED KINGDOM | 1998 | 267004698.909959 UNITED KINGDOM | 1997 | 453940209.275663 UNITED KINGDOM | 1996 | 460280227.543528 UNITED KINGDOM | 1995 | 454886977.576104 UNITED KINGDOM | 1994 | 454591250.929842 UNITED KINGDOM | 1993 | 453550035.277607 UNITED KINGDOM | 1992 | 461348077.331621 UNITED STATES | 1998 | 272124433.785057 UNITED STATES | 1997 | 468637523.892627 UNITED STATES | 1996 | 475257221.296252 UNITED STATES | 1995 | 463922448.506374 UNITED STATES | 1994 | 472312683.565188 UNITED STATES | 1993 | 469993039.099431 UNITED STATES | 1992 | 459278454.423826 VIETNAM | 1998 | 272769382.739343 VIETNAM | 1997 | 455205408.102038 VIETNAM | 1996 | 462492250.772073 VIETNAM | 1995 | 455139207.351723 VIETNAM | 1994 | 460108062.563483 VIETNAM | 1993 | 459688000.899671 VIETNAM | 1992 | 459232746.415275 (175 rows) COMMIT; COMMIT