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 '%plum%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267103131.996375 ALGERIA | 1997 | 446820214.460947 ALGERIA | 1996 | 463950471.468566 ALGERIA | 1995 | 459711109.231267 ALGERIA | 1994 | 457624253.531982 ALGERIA | 1993 | 463275572.474975 ALGERIA | 1992 | 460817307.507233 ARGENTINA | 1998 | 269933896.175024 ARGENTINA | 1997 | 467746826.121549 ARGENTINA | 1996 | 466841860.262463 ARGENTINA | 1995 | 461041902.331926 ARGENTINA | 1994 | 465801898.863436 ARGENTINA | 1993 | 465326173.380821 ARGENTINA | 1992 | 456443526.958656 BRAZIL | 1998 | 273563525.57863 BRAZIL | 1997 | 483136607.747039 BRAZIL | 1996 | 473307277.385701 BRAZIL | 1995 | 468043217.031614 BRAZIL | 1994 | 469585332.349399 BRAZIL | 1993 | 483047590.817595 BRAZIL | 1992 | 474841026.353683 CANADA | 1998 | 270954411.893346 CANADA | 1997 | 468999021.245439 CANADA | 1996 | 475490668.239627 CANADA | 1995 | 473929072.822014 CANADA | 1994 | 483573649.070868 CANADA | 1993 | 479829174.033634 CANADA | 1992 | 472571864.152238 CHINA | 1998 | 273814319.085624 CHINA | 1997 | 465560839.105981 CHINA | 1996 | 476271421.40991 CHINA | 1995 | 472770258.646082 CHINA | 1994 | 475914657.106987 CHINA | 1993 | 467694999.425096 CHINA | 1992 | 468853928.26726 EGYPT | 1998 | 274586502.699782 EGYPT | 1997 | 465067673.73162 EGYPT | 1996 | 471504137.976317 EGYPT | 1995 | 464226579.081266 EGYPT | 1994 | 454828970.594404 EGYPT | 1993 | 461754044.514681 EGYPT | 1992 | 465738615.130989 ETHIOPIA | 1998 | 268635888.017596 ETHIOPIA | 1997 | 457218727.645196 ETHIOPIA | 1996 | 466662582.768587 ETHIOPIA | 1995 | 466178496.20854 ETHIOPIA | 1994 | 464116767.286979 ETHIOPIA | 1993 | 456931714.635107 ETHIOPIA | 1992 | 467616846.212803 FRANCE | 1998 | 271302226.26695 FRANCE | 1997 | 463802323.425031 FRANCE | 1996 | 467320839.941549 FRANCE | 1995 | 470786222.802772 FRANCE | 1994 | 464684705.196364 FRANCE | 1993 | 465191358.617589 FRANCE | 1992 | 464760211.401339 GERMANY | 1998 | 272949135.212416 GERMANY | 1997 | 464052313.665084 GERMANY | 1996 | 465369345.951536 GERMANY | 1995 | 469411793.89984 GERMANY | 1994 | 469574783.936414 GERMANY | 1993 | 469194972.185872 GERMANY | 1992 | 472787061.190812 INDIA | 1998 | 271342738.746392 INDIA | 1997 | 468603005.286019 INDIA | 1996 | 477636538.001384 INDIA | 1995 | 476289770.865097 INDIA | 1994 | 467176885.374718 INDIA | 1993 | 470908253.331832 INDIA | 1992 | 478540532.701619 INDONESIA | 1998 | 271708786.720324 INDONESIA | 1997 | 468051906.965697 INDONESIA | 1996 | 463597830.691458 INDONESIA | 1995 | 462052720.600758 INDONESIA | 1994 | 459070183.293967 INDONESIA | 1993 | 465638154.545221 INDONESIA | 1992 | 469113308.613853 IRAN | 1998 | 274964709.980138 IRAN | 1997 | 468907390.652083 IRAN | 1996 | 464238077.185961 IRAN | 1995 | 468999547.25397 IRAN | 1994 | 464465570.322334 IRAN | 1993 | 463734537.952756 IRAN | 1992 | 461483718.911417 IRAQ | 1998 | 271619012.12836 IRAQ | 1997 | 469552867.257625 IRAQ | 1996 | 482321898.592392 IRAQ | 1995 | 460646822.782517 IRAQ | 1994 | 470559819.84966 IRAQ | 1993 | 474377325.952047 IRAQ | 1992 | 466691258.103752 JAPAN | 1998 | 274492019.74957 JAPAN | 1997 | 469893579.91832 JAPAN | 1996 | 471640143.984014 JAPAN | 1995 | 467225291.183641 JAPAN | 1994 | 459297394.870089 JAPAN | 1993 | 467978288.604223 JAPAN | 1992 | 461591286.464215 JORDAN | 1998 | 269556989.083624 JORDAN | 1997 | 470435317.742451 JORDAN | 1996 | 463139165.411769 JORDAN | 1995 | 462635115.208258 JORDAN | 1994 | 458249013.438507 JORDAN | 1993 | 471468924.402523 JORDAN | 1992 | 463767055.313561 KENYA | 1998 | 281125582.279362 KENYA | 1997 | 473625202.067297 KENYA | 1996 | 475469152.948384 KENYA | 1995 | 477686618.649232 KENYA | 1994 | 481207597.26806 KENYA | 1993 | 475010521.122049 KENYA | 1992 | 477798324.783145 MOROCCO | 1998 | 276509347.865943 MOROCCO | 1997 | 472165928.603131 MOROCCO | 1996 | 463192780.961198 MOROCCO | 1995 | 464526783.206433 MOROCCO | 1994 | 470321683.016768 MOROCCO | 1993 | 463843490.819781 MOROCCO | 1992 | 464806010.497175 MOZAMBIQUE | 1998 | 266305745.662682 MOZAMBIQUE | 1997 | 454960889.010143 MOZAMBIQUE | 1996 | 453866661.104822 MOZAMBIQUE | 1995 | 449450461.032381 MOZAMBIQUE | 1994 | 451690958.927114 MOZAMBIQUE | 1993 | 463516753.202323 MOZAMBIQUE | 1992 | 460017892.977064 PERU | 1998 | 271916143.819996 PERU | 1997 | 461303086.068007 PERU | 1996 | 459382929.532869 PERU | 1995 | 455479332.888181 PERU | 1994 | 455875626.693645 PERU | 1993 | 463878188.717877 PERU | 1992 | 463197569.363337 ROMANIA | 1998 | 275751686.172263 ROMANIA | 1997 | 456608643.747549 ROMANIA | 1996 | 464188116.107742 ROMANIA | 1995 | 468299671.102591 ROMANIA | 1994 | 469345276.771348 ROMANIA | 1993 | 459897340.351404 ROMANIA | 1992 | 456079011.410475 RUSSIA | 1998 | 277827851.952266 RUSSIA | 1997 | 475908156.382273 RUSSIA | 1996 | 471403829.07077 RUSSIA | 1995 | 477564104.811989 RUSSIA | 1994 | 471601770.630597 RUSSIA | 1993 | 463665866.048553 RUSSIA | 1992 | 467770506.797521 SAUDI ARABIA | 1998 | 272976811.375472 SAUDI ARABIA | 1997 | 468028195.210017 SAUDI ARABIA | 1996 | 468758915.209421 SAUDI ARABIA | 1995 | 469193999.717248 SAUDI ARABIA | 1994 | 462949562.913973 SAUDI ARABIA | 1993 | 465515525.368858 SAUDI ARABIA | 1992 | 471562057.20581 UNITED KINGDOM | 1998 | 267836493.531742 UNITED KINGDOM | 1997 | 466097903.980118 UNITED KINGDOM | 1996 | 459998199.301678 UNITED KINGDOM | 1995 | 463406515.507565 UNITED KINGDOM | 1994 | 455626814.539576 UNITED KINGDOM | 1993 | 461152678.606241 UNITED KINGDOM | 1992 | 464343069.861031 UNITED STATES | 1998 | 276859800.86578 UNITED STATES | 1997 | 481505488.98062 UNITED STATES | 1996 | 473785515.171509 UNITED STATES | 1995 | 472563718.197956 UNITED STATES | 1994 | 473702807.847883 UNITED STATES | 1993 | 473586032.648548 UNITED STATES | 1992 | 471842980.045974 VIETNAM | 1998 | 274919202.25778 VIETNAM | 1997 | 468291077.264452 VIETNAM | 1996 | 481265510.359639 VIETNAM | 1995 | 467401356.879387 VIETNAM | 1994 | 476040779.808304 VIETNAM | 1993 | 476888433.791275 VIETNAM | 1992 | 471566898.443224 (175 rows) COMMIT; COMMIT