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 '%chartreuse%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268642334.188486 ALGERIA | 1997 | 451065341.476511 ALGERIA | 1996 | 454705468.320273 ALGERIA | 1995 | 455786158.689735 ALGERIA | 1994 | 446613992.215038 ALGERIA | 1993 | 449187576.813355 ALGERIA | 1992 | 450648571.773537 ARGENTINA | 1998 | 278599908.889342 ARGENTINA | 1997 | 474731876.32005 ARGENTINA | 1996 | 477654209.625092 ARGENTINA | 1995 | 476107424.956669 ARGENTINA | 1994 | 472744980.043143 ARGENTINA | 1993 | 477978292.574631 ARGENTINA | 1992 | 472275894.351026 BRAZIL | 1998 | 268825452.090154 BRAZIL | 1997 | 462022009.602556 BRAZIL | 1996 | 462128644.713978 BRAZIL | 1995 | 454894717.808195 BRAZIL | 1994 | 458633933.848062 BRAZIL | 1993 | 453251112.258814 BRAZIL | 1992 | 461496255.266128 CANADA | 1998 | 273905854.267146 CANADA | 1997 | 469743272.897939 CANADA | 1996 | 470579022.825665 CANADA | 1995 | 465269554.910409 CANADA | 1994 | 465517729.515548 CANADA | 1993 | 465716967.471745 CANADA | 1992 | 469174971.598985 CHINA | 1998 | 278437394.91518 CHINA | 1997 | 466960842.53159 CHINA | 1996 | 470135364.977417 CHINA | 1995 | 456934078.248884 CHINA | 1994 | 463055128.284582 CHINA | 1993 | 461314594.049567 CHINA | 1992 | 462680081.924337 EGYPT | 1998 | 274825395.635107 EGYPT | 1997 | 456456925.386585 EGYPT | 1996 | 462850609.209788 EGYPT | 1995 | 466120738.492158 EGYPT | 1994 | 461708653.152515 EGYPT | 1993 | 466356618.739904 EGYPT | 1992 | 465488488.919309 ETHIOPIA | 1998 | 267104708.788781 ETHIOPIA | 1997 | 453993799.122387 ETHIOPIA | 1996 | 459797328.532422 ETHIOPIA | 1995 | 459128072.878284 ETHIOPIA | 1994 | 464830234.153014 ETHIOPIA | 1993 | 461330893.431406 ETHIOPIA | 1992 | 454974704.146032 FRANCE | 1998 | 271080873.355474 FRANCE | 1997 | 459257844.225522 FRANCE | 1996 | 460370151.249947 FRANCE | 1995 | 460453201.676746 FRANCE | 1994 | 456961709.288604 FRANCE | 1993 | 461222855.700885 FRANCE | 1992 | 454274837.436339 GERMANY | 1998 | 278830371.24855 GERMANY | 1997 | 470717993.639905 GERMANY | 1996 | 481191140.936703 GERMANY | 1995 | 475947170.247651 GERMANY | 1994 | 470490575.217746 GERMANY | 1993 | 473698408.266831 GERMANY | 1992 | 470674783.411758 INDIA | 1998 | 268923464.573038 INDIA | 1997 | 469522099.995578 INDIA | 1996 | 479226363.660077 INDIA | 1995 | 465781891.208575 INDIA | 1994 | 469163166.201737 INDIA | 1993 | 475351814.076484 INDIA | 1992 | 462152782.423649 INDONESIA | 1998 | 279314689.568818 INDONESIA | 1997 | 456587834.738608 INDONESIA | 1996 | 466689458.095316 INDONESIA | 1995 | 464665490.89285 INDONESIA | 1994 | 466010364.662724 INDONESIA | 1993 | 461666827.402495 INDONESIA | 1992 | 470669007.71907 IRAN | 1998 | 267200039.349977 IRAN | 1997 | 453255321.963693 IRAN | 1996 | 460385427.74975 IRAN | 1995 | 457286002.194619 IRAN | 1994 | 464780978.23074 IRAN | 1993 | 460414940.060999 IRAN | 1992 | 450528640.5677 IRAQ | 1998 | 280417590.335688 IRAQ | 1997 | 473563263.478823 IRAQ | 1996 | 470137316.471167 IRAQ | 1995 | 468160800.680368 IRAQ | 1994 | 465302541.878014 IRAQ | 1993 | 469639652.408764 IRAQ | 1992 | 477783320.250437 JAPAN | 1998 | 273722589.409761 JAPAN | 1997 | 471054642.200125 JAPAN | 1996 | 465468106.399389 JAPAN | 1995 | 462865357.472584 JAPAN | 1994 | 469566717.30882 JAPAN | 1993 | 466000286.200104 JAPAN | 1992 | 460397104.826658 JORDAN | 1998 | 269363278.983075 JORDAN | 1997 | 460014460.129709 JORDAN | 1996 | 459483464.775799 JORDAN | 1995 | 451112282.141859 JORDAN | 1994 | 455177912.149248 JORDAN | 1993 | 451626313.274918 JORDAN | 1992 | 456303979.74512 KENYA | 1998 | 271533302.310216 KENYA | 1997 | 463327435.496813 KENYA | 1996 | 470759660.827283 KENYA | 1995 | 476600710.254498 KENYA | 1994 | 465210712.284653 KENYA | 1993 | 463734842.862207 KENYA | 1992 | 475718034.597064 MOROCCO | 1998 | 281329201.846224 MOROCCO | 1997 | 468472172.391185 MOROCCO | 1996 | 468699239.5263 MOROCCO | 1995 | 464790262.639026 MOROCCO | 1994 | 468668969.661791 MOROCCO | 1993 | 469843857.643695 MOROCCO | 1992 | 468977875.298404 MOZAMBIQUE | 1998 | 274919445.860309 MOZAMBIQUE | 1997 | 469317881.557941 MOZAMBIQUE | 1996 | 466921104.586978 MOZAMBIQUE | 1995 | 472982659.459965 MOZAMBIQUE | 1994 | 467307044.576879 MOZAMBIQUE | 1993 | 467648317.531961 MOZAMBIQUE | 1992 | 467210006.988309 PERU | 1998 | 275257647.126137 PERU | 1997 | 464343176.04402 PERU | 1996 | 463813263.165619 PERU | 1995 | 460254673.967135 PERU | 1994 | 471287631.520614 PERU | 1993 | 461771210.438228 PERU | 1992 | 468146065.966188 ROMANIA | 1998 | 269046404.658887 ROMANIA | 1997 | 466176400.758118 ROMANIA | 1996 | 468587199.826146 ROMANIA | 1995 | 469707004.774016 ROMANIA | 1994 | 460772662.815662 ROMANIA | 1993 | 463902770.534498 ROMANIA | 1992 | 461738543.68987 RUSSIA | 1998 | 277129345.787209 RUSSIA | 1997 | 470575816.255486 RUSSIA | 1996 | 468089092.112323 RUSSIA | 1995 | 471254146.998881 RUSSIA | 1994 | 470039852.813356 RUSSIA | 1993 | 469178273.803171 RUSSIA | 1992 | 473988995.085205 SAUDI ARABIA | 1998 | 280491558.540834 SAUDI ARABIA | 1997 | 465606313.204043 SAUDI ARABIA | 1996 | 465465338.381478 SAUDI ARABIA | 1995 | 466780369.401103 SAUDI ARABIA | 1994 | 464545112.618654 SAUDI ARABIA | 1993 | 462982822.271703 SAUDI ARABIA | 1992 | 464193501.95506 UNITED KINGDOM | 1998 | 272345455.835214 UNITED KINGDOM | 1997 | 460433827.665144 UNITED KINGDOM | 1996 | 464983423.360966 UNITED KINGDOM | 1995 | 459836831.320913 UNITED KINGDOM | 1994 | 461895772.510013 UNITED KINGDOM | 1993 | 456397277.92417 UNITED KINGDOM | 1992 | 458507828.40225 UNITED STATES | 1998 | 270030672.162708 UNITED STATES | 1997 | 455925761.747865 UNITED STATES | 1996 | 459142667.743164 UNITED STATES | 1995 | 465947892.286162 UNITED STATES | 1994 | 470254225.999127 UNITED STATES | 1993 | 457297837.148651 UNITED STATES | 1992 | 454683830.391124 VIETNAM | 1998 | 273934841.010089 VIETNAM | 1997 | 460722837.440542 VIETNAM | 1996 | 468421210.560198 VIETNAM | 1995 | 464842319.424081 VIETNAM | 1994 | 466201189.564867 VIETNAM | 1993 | 464509229.806456 VIETNAM | 1992 | 462541352.366278 (175 rows) COMMIT; COMMIT