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 '%blanched%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263810497.834003 ALGERIA | 1997 | 452018640.658976 ALGERIA | 1996 | 453547178.402094 ALGERIA | 1995 | 454422784.337873 ALGERIA | 1994 | 460658979.647408 ALGERIA | 1993 | 454301677.274121 ALGERIA | 1992 | 457011422.444921 ARGENTINA | 1998 | 278048815.085107 ARGENTINA | 1997 | 468300106.416727 ARGENTINA | 1996 | 471417212.013326 ARGENTINA | 1995 | 465227089.934351 ARGENTINA | 1994 | 466000783.183453 ARGENTINA | 1993 | 466270167.959252 ARGENTINA | 1992 | 473923173.027016 BRAZIL | 1998 | 271019438.669838 BRAZIL | 1997 | 470843867.157609 BRAZIL | 1996 | 475629909.624612 BRAZIL | 1995 | 464367733.078842 BRAZIL | 1994 | 465841727.012957 BRAZIL | 1993 | 464668643.22985 BRAZIL | 1992 | 471455533.25835 CANADA | 1998 | 280167213.086047 CANADA | 1997 | 467400374.579549 CANADA | 1996 | 472539302.251815 CANADA | 1995 | 465577368.85918 CANADA | 1994 | 473174229.8384 CANADA | 1993 | 482378773.127653 CANADA | 1992 | 476698340.815838 CHINA | 1998 | 269951761.573184 CHINA | 1997 | 458065038.84001 CHINA | 1996 | 456578355.273215 CHINA | 1995 | 466601087.149824 CHINA | 1994 | 460653111.961104 CHINA | 1993 | 456100081.09258 CHINA | 1992 | 457883526.876945 EGYPT | 1998 | 265724220.265805 EGYPT | 1997 | 467010927.179347 EGYPT | 1996 | 463421930.469568 EGYPT | 1995 | 470673256.395466 EGYPT | 1994 | 465029030.03815 EGYPT | 1993 | 467333717.556697 EGYPT | 1992 | 468296178.672713 ETHIOPIA | 1998 | 270749862.229699 ETHIOPIA | 1997 | 449572895.159855 ETHIOPIA | 1996 | 451759405.363327 ETHIOPIA | 1995 | 449783686.220272 ETHIOPIA | 1994 | 461255010.88017 ETHIOPIA | 1993 | 455693944.567488 ETHIOPIA | 1992 | 460818017.330316 FRANCE | 1998 | 272313085.878733 FRANCE | 1997 | 467896144.408776 FRANCE | 1996 | 474045285.606703 FRANCE | 1995 | 459464717.90208 FRANCE | 1994 | 462846170.978529 FRANCE | 1993 | 463520882.417734 FRANCE | 1992 | 464845173.941265 GERMANY | 1998 | 282587401.301326 GERMANY | 1997 | 477341093.66745 GERMANY | 1996 | 483181086.982697 GERMANY | 1995 | 482053191.541331 GERMANY | 1994 | 478379507.320096 GERMANY | 1993 | 478443814.603109 GERMANY | 1992 | 476465808.478512 INDIA | 1998 | 276243619.16021 INDIA | 1997 | 470015625.217793 INDIA | 1996 | 474024154.864747 INDIA | 1995 | 473491410.640139 INDIA | 1994 | 474190940.085922 INDIA | 1993 | 469270510.120953 INDIA | 1992 | 483529504.300182 INDONESIA | 1998 | 275416156.866096 INDONESIA | 1997 | 460798986.196659 INDONESIA | 1996 | 465619307.90606 INDONESIA | 1995 | 467989441.090572 INDONESIA | 1994 | 463657415.279441 INDONESIA | 1993 | 457789559.55727 INDONESIA | 1992 | 456816267.993605 IRAN | 1998 | 268056926.030894 IRAN | 1997 | 458240863.491653 IRAN | 1996 | 460104955.055753 IRAN | 1995 | 463974224.368817 IRAN | 1994 | 458216989.651689 IRAN | 1993 | 456623924.046351 IRAN | 1992 | 462013260.252992 IRAQ | 1998 | 278540494.072888 IRAQ | 1997 | 466072039.534567 IRAQ | 1996 | 471948940.985572 IRAQ | 1995 | 468675135.341013 IRAQ | 1994 | 470602979.571149 IRAQ | 1993 | 464155521.809499 IRAQ | 1992 | 472498903.3525 JAPAN | 1998 | 274793212.125221 JAPAN | 1997 | 464785922.080662 JAPAN | 1996 | 466007246.310573 JAPAN | 1995 | 461732354.644102 JAPAN | 1994 | 458170107.285408 JAPAN | 1993 | 469369526.431723 JAPAN | 1992 | 457450738.4057 JORDAN | 1998 | 272294442.261321 JORDAN | 1997 | 463843276.903595 JORDAN | 1996 | 466353905.335397 JORDAN | 1995 | 461856157.363031 JORDAN | 1994 | 460328048.786134 JORDAN | 1993 | 457989690.000723 JORDAN | 1992 | 461585420.004555 KENYA | 1998 | 273117996.9583 KENYA | 1997 | 469567127.464891 KENYA | 1996 | 475917518.256301 KENYA | 1995 | 466103913.618678 KENYA | 1994 | 468978140.906846 KENYA | 1993 | 471235779.165753 KENYA | 1992 | 468665587.606326 MOROCCO | 1998 | 272516733.151108 MOROCCO | 1997 | 470117822.876177 MOROCCO | 1996 | 476578435.774456 MOROCCO | 1995 | 477076513.866214 MOROCCO | 1994 | 477686765.16779 MOROCCO | 1993 | 470712037.142299 MOROCCO | 1992 | 471087565.388044 MOZAMBIQUE | 1998 | 272666057.217099 MOZAMBIQUE | 1997 | 459042158.02011 MOZAMBIQUE | 1996 | 467817971.94953 MOZAMBIQUE | 1995 | 459462918.231227 MOZAMBIQUE | 1994 | 461998720.726531 MOZAMBIQUE | 1993 | 461952577.368388 MOZAMBIQUE | 1992 | 459209565.932015 PERU | 1998 | 268387520.051741 PERU | 1997 | 459192104.079418 PERU | 1996 | 461338395.676054 PERU | 1995 | 470101224.531323 PERU | 1994 | 470327441.566529 PERU | 1993 | 467461564.806747 PERU | 1992 | 473961065.976689 ROMANIA | 1998 | 271964415.744371 ROMANIA | 1997 | 463864212.496898 ROMANIA | 1996 | 461858717.674754 ROMANIA | 1995 | 460972738.193492 ROMANIA | 1994 | 468131027.204436 ROMANIA | 1993 | 463129892.586329 ROMANIA | 1992 | 458897654.215409 RUSSIA | 1998 | 272869089.026657 RUSSIA | 1997 | 472039570.87494 RUSSIA | 1996 | 472788140.89827 RUSSIA | 1995 | 463689269.771108 RUSSIA | 1994 | 464842399.394728 RUSSIA | 1993 | 460120802.491191 RUSSIA | 1992 | 473191207.672907 SAUDI ARABIA | 1998 | 274578944.270371 SAUDI ARABIA | 1997 | 469279206.765702 SAUDI ARABIA | 1996 | 466250161.915762 SAUDI ARABIA | 1995 | 470772789.230168 SAUDI ARABIA | 1994 | 460193405.892965 SAUDI ARABIA | 1993 | 463899642.07253 SAUDI ARABIA | 1992 | 469007992.58334 UNITED KINGDOM | 1998 | 272508198.648444 UNITED KINGDOM | 1997 | 469887256.716287 UNITED KINGDOM | 1996 | 471602132.438222 UNITED KINGDOM | 1995 | 464840739.479304 UNITED KINGDOM | 1994 | 462627681.57564 UNITED KINGDOM | 1993 | 463072962.818442 UNITED KINGDOM | 1992 | 464262548.844785 UNITED STATES | 1998 | 269231437.640349 UNITED STATES | 1997 | 466521220.109191 UNITED STATES | 1996 | 469585731.044231 UNITED STATES | 1995 | 474162526.705508 UNITED STATES | 1994 | 467840941.904161 UNITED STATES | 1993 | 470018588.307843 UNITED STATES | 1992 | 462952659.302031 VIETNAM | 1998 | 263143527.300284 VIETNAM | 1997 | 460585922.089708 VIETNAM | 1996 | 475806165.698368 VIETNAM | 1995 | 460638563.476204 VIETNAM | 1994 | 464412242.026737 VIETNAM | 1993 | 465692756.17549 VIETNAM | 1992 | 460470809.445111 (175 rows) COMMIT; COMMIT