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 | 262511652.509615 ALGERIA | 1997 | 458610698.108058 ALGERIA | 1996 | 461237705.998445 ALGERIA | 1995 | 461242560.226238 ALGERIA | 1994 | 459602458.163446 ALGERIA | 1993 | 454452953.749263 ALGERIA | 1992 | 468916885.600135 ARGENTINA | 1998 | 266748105.904896 ARGENTINA | 1997 | 454547066.236369 ARGENTINA | 1996 | 460453773.488594 ARGENTINA | 1995 | 454095658.112563 ARGENTINA | 1994 | 455014567.704108 ARGENTINA | 1993 | 458979802.246587 ARGENTINA | 1992 | 458723790.059821 BRAZIL | 1998 | 272672704.996907 BRAZIL | 1997 | 467826732.276018 BRAZIL | 1996 | 475170933.837011 BRAZIL | 1995 | 463372119.500071 BRAZIL | 1994 | 462914738.122775 BRAZIL | 1993 | 463729429.85168 BRAZIL | 1992 | 471769789.010336 CANADA | 1998 | 271748966.013117 CANADA | 1997 | 470393424.678135 CANADA | 1996 | 465017932.655996 CANADA | 1995 | 461103765.613417 CANADA | 1994 | 464111544.305877 CANADA | 1993 | 475976656.163099 CANADA | 1992 | 461402204.140514 CHINA | 1998 | 277301209.361818 CHINA | 1997 | 466563454.018081 CHINA | 1996 | 465358595.953267 CHINA | 1995 | 468458128.525865 CHINA | 1994 | 472655399.123049 CHINA | 1993 | 472139765.940526 CHINA | 1992 | 467828512.910588 EGYPT | 1998 | 277711491.702827 EGYPT | 1997 | 462846484.770323 EGYPT | 1996 | 462586151.313466 EGYPT | 1995 | 461507671.945935 EGYPT | 1994 | 458923267.577336 EGYPT | 1993 | 459270933.39711 EGYPT | 1992 | 467621767.233853 ETHIOPIA | 1998 | 275344805.885924 ETHIOPIA | 1997 | 460271013.782123 ETHIOPIA | 1996 | 464022037.385686 ETHIOPIA | 1995 | 458139789.171251 ETHIOPIA | 1994 | 463418493.119404 ETHIOPIA | 1993 | 457897492.04005 ETHIOPIA | 1992 | 456894304.962771 FRANCE | 1998 | 271924147.592773 FRANCE | 1997 | 456165524.290904 FRANCE | 1996 | 456813494.075993 FRANCE | 1995 | 460000510.78003 FRANCE | 1994 | 455611367.048631 FRANCE | 1993 | 455454721.110362 FRANCE | 1992 | 450398517.96917 GERMANY | 1998 | 276056795.91261 GERMANY | 1997 | 472728912.087768 GERMANY | 1996 | 471427393.62605 GERMANY | 1995 | 468904866.566405 GERMANY | 1994 | 468306174.586972 GERMANY | 1993 | 465619946.75082 GERMANY | 1992 | 470035470.277384 INDIA | 1998 | 279434265.542747 INDIA | 1997 | 477900314.366529 INDIA | 1996 | 474103081.685843 INDIA | 1995 | 474377357.401981 INDIA | 1994 | 474524002.011711 INDIA | 1993 | 478022597.826341 INDIA | 1992 | 483037279.654569 INDONESIA | 1998 | 265345676.119091 INDONESIA | 1997 | 454771481.503835 INDONESIA | 1996 | 463408140.662456 INDONESIA | 1995 | 454770337.166407 INDONESIA | 1994 | 457762923.0139 INDONESIA | 1993 | 463555516.445964 INDONESIA | 1992 | 462264027.87168 IRAN | 1998 | 267566239.593662 IRAN | 1997 | 466989836.36154 IRAN | 1996 | 469057607.700852 IRAN | 1995 | 465032836.172127 IRAN | 1994 | 461019564.049591 IRAN | 1993 | 464510320.020058 IRAN | 1992 | 465288804.746612 IRAQ | 1998 | 278892374.991554 IRAQ | 1997 | 470413380.447002 IRAQ | 1996 | 466633474.716128 IRAQ | 1995 | 463659214.059776 IRAQ | 1994 | 465840485.596693 IRAQ | 1993 | 466311597.464343 IRAQ | 1992 | 470829717.817535 JAPAN | 1998 | 269557498.739517 JAPAN | 1997 | 465141028.780258 JAPAN | 1996 | 469758288.647602 JAPAN | 1995 | 462449915.207046 JAPAN | 1994 | 468042650.889089 JAPAN | 1993 | 470078600.322131 JAPAN | 1992 | 460726616.702014 JORDAN | 1998 | 271005878.981588 JORDAN | 1997 | 460078290.123992 JORDAN | 1996 | 459649947.76962 JORDAN | 1995 | 461845110.035655 JORDAN | 1994 | 459413493.890473 JORDAN | 1993 | 457372811.437882 JORDAN | 1992 | 459730100.092983 KENYA | 1998 | 279375967.342595 KENYA | 1997 | 468469008.587193 KENYA | 1996 | 471347309.343433 KENYA | 1995 | 470518537.030669 KENYA | 1994 | 480242819.499857 KENYA | 1993 | 465952976.461285 KENYA | 1992 | 477227362.907974 MOROCCO | 1998 | 271075881.638771 MOROCCO | 1997 | 459820830.808524 MOROCCO | 1996 | 461617897.810397 MOROCCO | 1995 | 460720019.176224 MOROCCO | 1994 | 461215762.377736 MOROCCO | 1993 | 462262592.101077 MOROCCO | 1992 | 460025698.217856 MOZAMBIQUE | 1998 | 268789762.793659 MOZAMBIQUE | 1997 | 456897017.520005 MOZAMBIQUE | 1996 | 464000669.349422 MOZAMBIQUE | 1995 | 456737897.54099 MOZAMBIQUE | 1994 | 451635319.491386 MOZAMBIQUE | 1993 | 465355722.026567 MOZAMBIQUE | 1992 | 461787964.410425 PERU | 1998 | 274920290.771678 PERU | 1997 | 476242823.307154 PERU | 1996 | 473191265.454046 PERU | 1995 | 478634479.691326 PERU | 1994 | 468461307.367929 PERU | 1993 | 467037529.380055 PERU | 1992 | 469496459.443973 ROMANIA | 1998 | 272173189.119631 ROMANIA | 1997 | 459759197.097969 ROMANIA | 1996 | 471877329.021428 ROMANIA | 1995 | 467328231.721659 ROMANIA | 1994 | 464523337.227354 ROMANIA | 1993 | 457052340.269672 ROMANIA | 1992 | 466237609.882367 RUSSIA | 1998 | 275092271.097387 RUSSIA | 1997 | 467757578.393997 RUSSIA | 1996 | 473656966.272851 RUSSIA | 1995 | 469341241.264503 RUSSIA | 1994 | 466825546.501766 RUSSIA | 1993 | 475158204.493679 RUSSIA | 1992 | 466201787.068905 SAUDI ARABIA | 1998 | 265104739.391012 SAUDI ARABIA | 1997 | 470393883.38836 SAUDI ARABIA | 1996 | 467981364.206376 SAUDI ARABIA | 1995 | 465510765.065969 SAUDI ARABIA | 1994 | 472047917.433402 SAUDI ARABIA | 1993 | 465384867.024297 SAUDI ARABIA | 1992 | 476959606.618221 UNITED KINGDOM | 1998 | 267223585.103988 UNITED KINGDOM | 1997 | 454826002.864003 UNITED KINGDOM | 1996 | 460301641.10463 UNITED KINGDOM | 1995 | 454902896.932991 UNITED KINGDOM | 1994 | 455379995.197003 UNITED KINGDOM | 1993 | 454400568.888235 UNITED KINGDOM | 1992 | 461871064.602373 UNITED STATES | 1998 | 272417256.227119 UNITED STATES | 1997 | 468972944.350845 UNITED STATES | 1996 | 476024532.303091 UNITED STATES | 1995 | 464194576.660986 UNITED STATES | 1994 | 472634969.996932 UNITED STATES | 1993 | 470435166.244901 UNITED STATES | 1992 | 459356932.298524 VIETNAM | 1998 | 272702556.021231 VIETNAM | 1997 | 455658217.133936 VIETNAM | 1996 | 463057494.418345 VIETNAM | 1995 | 455788420.281079 VIETNAM | 1994 | 460653814.380621 VIETNAM | 1993 | 460389700.840021 VIETNAM | 1992 | 459948139.765099 (175 rows) COMMIT; COMMIT