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 '%lemon%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265469205.706261 ALGERIA | 1997 | 445811692.609052 ALGERIA | 1996 | 455359642.598442 ALGERIA | 1995 | 451343248.84593 ALGERIA | 1994 | 458204853.564023 ALGERIA | 1993 | 452849304.136064 ALGERIA | 1992 | 446425055.865621 ARGENTINA | 1998 | 277030387.007993 ARGENTINA | 1997 | 463173909.097606 ARGENTINA | 1996 | 463724678.395882 ARGENTINA | 1995 | 466367064.454317 ARGENTINA | 1994 | 469325817.456739 ARGENTINA | 1993 | 465064982.22632 ARGENTINA | 1992 | 458400525.759874 BRAZIL | 1998 | 280535114.488927 BRAZIL | 1997 | 478240765.126737 BRAZIL | 1996 | 479746117.305641 BRAZIL | 1995 | 465161856.706397 BRAZIL | 1994 | 464838389.6085 BRAZIL | 1993 | 473628829.000517 BRAZIL | 1992 | 474677246.592176 CANADA | 1998 | 276179945.625629 CANADA | 1997 | 475108291.490792 CANADA | 1996 | 480210305.105592 CANADA | 1995 | 476937507.57004 CANADA | 1994 | 480132337.181972 CANADA | 1993 | 473533723.585696 CANADA | 1992 | 476025417.511331 CHINA | 1998 | 265809654.725299 CHINA | 1997 | 466476714.751776 CHINA | 1996 | 468383441.492849 CHINA | 1995 | 461897826.58261 CHINA | 1994 | 457815288.227179 CHINA | 1993 | 464075453.461588 CHINA | 1992 | 462137015.9314 EGYPT | 1998 | 267987147.505645 EGYPT | 1997 | 458016268.41623 EGYPT | 1996 | 462481301.976622 EGYPT | 1995 | 454198385.622396 EGYPT | 1994 | 466514392.065642 EGYPT | 1993 | 471762020.133743 EGYPT | 1992 | 464465176.262314 ETHIOPIA | 1998 | 274839946.780207 ETHIOPIA | 1997 | 460548894.299939 ETHIOPIA | 1996 | 469611601.3772 ETHIOPIA | 1995 | 468495529.941351 ETHIOPIA | 1994 | 468407534.757632 ETHIOPIA | 1993 | 466202640.321721 ETHIOPIA | 1992 | 463537726.641465 FRANCE | 1998 | 268627540.675411 FRANCE | 1997 | 452518020.443184 FRANCE | 1996 | 466236988.877678 FRANCE | 1995 | 455735695.626012 FRANCE | 1994 | 460047601.568307 FRANCE | 1993 | 457611938.956872 FRANCE | 1992 | 449861860.696884 GERMANY | 1998 | 278452887.8912 GERMANY | 1997 | 478228408.893889 GERMANY | 1996 | 478108633.846691 GERMANY | 1995 | 474677148.843976 GERMANY | 1994 | 472002305.633603 GERMANY | 1993 | 466018663.193478 GERMANY | 1992 | 481865196.625384 INDIA | 1998 | 278871586.895744 INDIA | 1997 | 477670448.249452 INDIA | 1996 | 477143381.648022 INDIA | 1995 | 472789678.365464 INDIA | 1994 | 476135589.095675 INDIA | 1993 | 472055863.602121 INDIA | 1992 | 481887467.187051 INDONESIA | 1998 | 269663202.508121 INDONESIA | 1997 | 453170050.645152 INDONESIA | 1996 | 452241169.031356 INDONESIA | 1995 | 451738391.055856 INDONESIA | 1994 | 452720900.927324 INDONESIA | 1993 | 458719177.382418 INDONESIA | 1992 | 459801023.682352 IRAN | 1998 | 269766448.317746 IRAN | 1997 | 463098749.430833 IRAN | 1996 | 472165984.127012 IRAN | 1995 | 463540495.64186 IRAN | 1994 | 465044324.896526 IRAN | 1993 | 463626361.101351 IRAN | 1992 | 470943799.033258 IRAQ | 1998 | 279846320.558591 IRAQ | 1997 | 475938999.963552 IRAQ | 1996 | 472486715.858372 IRAQ | 1995 | 476626080.233698 IRAQ | 1994 | 476080880.317142 IRAQ | 1993 | 477273471.853244 IRAQ | 1992 | 472943493.579657 JAPAN | 1998 | 266421565.29673 JAPAN | 1997 | 463797022.867437 JAPAN | 1996 | 466315283.486159 JAPAN | 1995 | 465819587.713405 JAPAN | 1994 | 467178994.514299 JAPAN | 1993 | 466871505.825451 JAPAN | 1992 | 468202697.479584 JORDAN | 1998 | 273709901.174772 JORDAN | 1997 | 463201569.715589 JORDAN | 1996 | 469743386.967706 JORDAN | 1995 | 473229702.559631 JORDAN | 1994 | 465234112.936704 JORDAN | 1993 | 462357679.182856 JORDAN | 1992 | 455892833.832331 KENYA | 1998 | 274545446.335549 KENYA | 1997 | 465287239.674819 KENYA | 1996 | 468354066.248057 KENYA | 1995 | 466847338.472801 KENYA | 1994 | 465311870.253171 KENYA | 1993 | 469933186.270032 KENYA | 1992 | 471175084.120501 MOROCCO | 1998 | 270726460.270901 MOROCCO | 1997 | 464731232.300083 MOROCCO | 1996 | 465829842.69803 MOROCCO | 1995 | 464841674.849862 MOROCCO | 1994 | 473175084.752984 MOROCCO | 1993 | 467573260.889775 MOROCCO | 1992 | 466548970.474224 MOZAMBIQUE | 1998 | 268379646.507895 MOZAMBIQUE | 1997 | 464641477.605851 MOZAMBIQUE | 1996 | 466446663.708914 MOZAMBIQUE | 1995 | 464366449.947488 MOZAMBIQUE | 1994 | 456713232.607109 MOZAMBIQUE | 1993 | 455232501.265389 MOZAMBIQUE | 1992 | 458849832.651367 PERU | 1998 | 273633738.644784 PERU | 1997 | 471390318.224027 PERU | 1996 | 471295669.419807 PERU | 1995 | 472185753.845092 PERU | 1994 | 464370661.210991 PERU | 1993 | 471291868.741733 PERU | 1992 | 475503435.61431 ROMANIA | 1998 | 270281429.659467 ROMANIA | 1997 | 472376314.099468 ROMANIA | 1996 | 468431024.337318 ROMANIA | 1995 | 466145848.234081 ROMANIA | 1994 | 462355771.018195 ROMANIA | 1993 | 472812089.48924 ROMANIA | 1992 | 473983613.539259 RUSSIA | 1998 | 279059180.41166 RUSSIA | 1997 | 465346652.508781 RUSSIA | 1996 | 475391457.23272 RUSSIA | 1995 | 468739473.670657 RUSSIA | 1994 | 466689294.60615 RUSSIA | 1993 | 472851242.546263 RUSSIA | 1992 | 465425009.679201 SAUDI ARABIA | 1998 | 273945484.21121 SAUDI ARABIA | 1997 | 467596791.768313 SAUDI ARABIA | 1996 | 467862080.276371 SAUDI ARABIA | 1995 | 473201952.20132 SAUDI ARABIA | 1994 | 462222221.172711 SAUDI ARABIA | 1993 | 470683676.897733 SAUDI ARABIA | 1992 | 474823051.07957 UNITED KINGDOM | 1998 | 270596388.392412 UNITED KINGDOM | 1997 | 458866608.891001 UNITED KINGDOM | 1996 | 462024528.037773 UNITED KINGDOM | 1995 | 462371402.904288 UNITED KINGDOM | 1994 | 458558657.578463 UNITED KINGDOM | 1993 | 462047255.079921 UNITED KINGDOM | 1992 | 459467769.360077 UNITED STATES | 1998 | 273689218.707183 UNITED STATES | 1997 | 471843273.022325 UNITED STATES | 1996 | 480905903.671639 UNITED STATES | 1995 | 471086252.702102 UNITED STATES | 1994 | 473969244.977029 UNITED STATES | 1993 | 486421560.472484 UNITED STATES | 1992 | 477658832.381457 VIETNAM | 1998 | 269602196.042212 VIETNAM | 1997 | 462599792.171845 VIETNAM | 1996 | 472050122.78857 VIETNAM | 1995 | 466644463.710389 VIETNAM | 1994 | 471307304.182113 VIETNAM | 1993 | 470211585.651107 VIETNAM | 1992 | 465794146.530652 (175 rows) COMMIT; COMMIT