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 '%magenta%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263825537.733446 ALGERIA | 1997 | 459522398.681813 ALGERIA | 1996 | 467281686.401506 ALGERIA | 1995 | 463962926.370985 ALGERIA | 1994 | 460830033.077125 ALGERIA | 1993 | 460508245.599518 ALGERIA | 1992 | 452692060.41582 ARGENTINA | 1998 | 276264126.633469 ARGENTINA | 1997 | 467093266.391589 ARGENTINA | 1996 | 478763537.590606 ARGENTINA | 1995 | 472409848.500946 ARGENTINA | 1994 | 469457177.521769 ARGENTINA | 1993 | 465019563.558445 ARGENTINA | 1992 | 470247138.894991 BRAZIL | 1998 | 271693857.33032 BRAZIL | 1997 | 467656577.568216 BRAZIL | 1996 | 462179636.6337 BRAZIL | 1995 | 464719205.294748 BRAZIL | 1994 | 464761853.775891 BRAZIL | 1993 | 466972093.434288 BRAZIL | 1992 | 465266539.727455 CANADA | 1998 | 275269662.796419 CANADA | 1997 | 475609778.917813 CANADA | 1996 | 475462350.48304 CANADA | 1995 | 480569272.600292 CANADA | 1994 | 479452083.502972 CANADA | 1993 | 471954223.955292 CANADA | 1992 | 473256871.375474 CHINA | 1998 | 274396791.471973 CHINA | 1997 | 462407127.299753 CHINA | 1996 | 466722943.530442 CHINA | 1995 | 454778213.387649 CHINA | 1994 | 457602054.274185 CHINA | 1993 | 463536024.842939 CHINA | 1992 | 468051218.747092 EGYPT | 1998 | 272956156.147 EGYPT | 1997 | 476709886.67339 EGYPT | 1996 | 471604005.536289 EGYPT | 1995 | 461406313.978218 EGYPT | 1994 | 469546039.806263 EGYPT | 1993 | 461396333.631963 EGYPT | 1992 | 466644754.643918 ETHIOPIA | 1998 | 269483299.143128 ETHIOPIA | 1997 | 453292190.294295 ETHIOPIA | 1996 | 453771223.233021 ETHIOPIA | 1995 | 460497004.613277 ETHIOPIA | 1994 | 453873690.579944 ETHIOPIA | 1993 | 458857209.785017 ETHIOPIA | 1992 | 451946634.565075 FRANCE | 1998 | 271929590.394683 FRANCE | 1997 | 458782945.02396 FRANCE | 1996 | 462817125.623257 FRANCE | 1995 | 466604256.546201 FRANCE | 1994 | 462841404.101147 FRANCE | 1993 | 462266583.460182 FRANCE | 1992 | 452922379.698323 GERMANY | 1998 | 279760758.784249 GERMANY | 1997 | 482115143.524848 GERMANY | 1996 | 478564454.600778 GERMANY | 1995 | 474641816.505025 GERMANY | 1994 | 472251988.015767 GERMANY | 1993 | 480725153.612475 GERMANY | 1992 | 479207500.648916 INDIA | 1998 | 276623874.601138 INDIA | 1997 | 474048909.855765 INDIA | 1996 | 474501228.63742 INDIA | 1995 | 475186656.558215 INDIA | 1994 | 473768754.041376 INDIA | 1993 | 475710028.428589 INDIA | 1992 | 474263568.0876 INDONESIA | 1998 | 267843088.578915 INDONESIA | 1997 | 456273954.052498 INDONESIA | 1996 | 464844609.651452 INDONESIA | 1995 | 459694559.143107 INDONESIA | 1994 | 450057327.845415 INDONESIA | 1993 | 459546264.118137 INDONESIA | 1992 | 459176211.314595 IRAN | 1998 | 266915320.866764 IRAN | 1997 | 463399095.489397 IRAN | 1996 | 465162034.93529 IRAN | 1995 | 457867995.950497 IRAN | 1994 | 468034004.83144 IRAN | 1993 | 468851756.839684 IRAN | 1992 | 456091662.218473 IRAQ | 1998 | 271124918.075809 IRAQ | 1997 | 463249685.560504 IRAQ | 1996 | 460358121.28418 IRAQ | 1995 | 468519095.705163 IRAQ | 1994 | 467312644.822251 IRAQ | 1993 | 466124645.028074 IRAQ | 1992 | 477766019.359136 JAPAN | 1998 | 273822490.483948 JAPAN | 1997 | 468863460.568536 JAPAN | 1996 | 463636464.079772 JAPAN | 1995 | 466468298.422872 JAPAN | 1994 | 473541619.971255 JAPAN | 1993 | 464067778.75335 JAPAN | 1992 | 465223610.548977 JORDAN | 1998 | 265444265.53024 JORDAN | 1997 | 451619196.980884 JORDAN | 1996 | 449733269.692034 JORDAN | 1995 | 454746349.223896 JORDAN | 1994 | 440676918.864099 JORDAN | 1993 | 458331773.23429 JORDAN | 1992 | 446524079.59275 KENYA | 1998 | 273229554.992114 KENYA | 1997 | 462817193.703709 KENYA | 1996 | 465785763.276934 KENYA | 1995 | 463281069.876284 KENYA | 1994 | 465187078.959193 KENYA | 1993 | 465371573.395425 KENYA | 1992 | 467130801.955141 MOROCCO | 1998 | 269147082.227053 MOROCCO | 1997 | 465307852.411717 MOROCCO | 1996 | 473172461.955368 MOROCCO | 1995 | 465863314.522911 MOROCCO | 1994 | 470990190.479414 MOROCCO | 1993 | 465147242.429448 MOROCCO | 1992 | 475831762.773993 MOZAMBIQUE | 1998 | 266350098.205748 MOZAMBIQUE | 1997 | 465057810.46778 MOZAMBIQUE | 1996 | 456188462.342591 MOZAMBIQUE | 1995 | 456473723.669297 MOZAMBIQUE | 1994 | 459257956.836578 MOZAMBIQUE | 1993 | 458745610.62794 MOZAMBIQUE | 1992 | 459856349.190442 PERU | 1998 | 271095023.683999 PERU | 1997 | 466213737.258393 PERU | 1996 | 455803279.09334 PERU | 1995 | 460471975.76653 PERU | 1994 | 460288248.877644 PERU | 1993 | 467366415.533039 PERU | 1992 | 466291776.606173 ROMANIA | 1998 | 269939919.481879 ROMANIA | 1997 | 467986674.500235 ROMANIA | 1996 | 475666593.213214 ROMANIA | 1995 | 463359855.365142 ROMANIA | 1994 | 470538416.611486 ROMANIA | 1993 | 465669903.059079 ROMANIA | 1992 | 479559301.920173 RUSSIA | 1998 | 278832017.542695 RUSSIA | 1997 | 469748001.15423 RUSSIA | 1996 | 471899254.470919 RUSSIA | 1995 | 460986205.507944 RUSSIA | 1994 | 470617353.22811 RUSSIA | 1993 | 467543620.153655 RUSSIA | 1992 | 474441248.685578 SAUDI ARABIA | 1998 | 271884703.920664 SAUDI ARABIA | 1997 | 470056180.576184 SAUDI ARABIA | 1996 | 471014626.693078 SAUDI ARABIA | 1995 | 475843319.753308 SAUDI ARABIA | 1994 | 463259331.002874 SAUDI ARABIA | 1993 | 471441922.94078 SAUDI ARABIA | 1992 | 473022326.918059 UNITED KINGDOM | 1998 | 264352408.192342 UNITED KINGDOM | 1997 | 463940422.123235 UNITED KINGDOM | 1996 | 455077667.092604 UNITED KINGDOM | 1995 | 453734008.336008 UNITED KINGDOM | 1994 | 454236040.625913 UNITED KINGDOM | 1993 | 464528119.323511 UNITED KINGDOM | 1992 | 462353705.916788 UNITED STATES | 1998 | 276722926.560138 UNITED STATES | 1997 | 473504219.725526 UNITED STATES | 1996 | 472536607.545156 UNITED STATES | 1995 | 471746557.141829 UNITED STATES | 1994 | 469398720.933129 UNITED STATES | 1993 | 471547542.382854 UNITED STATES | 1992 | 473506377.609434 VIETNAM | 1998 | 274935956.951805 VIETNAM | 1997 | 463232798.851313 VIETNAM | 1996 | 466295113.749003 VIETNAM | 1995 | 467907641.449719 VIETNAM | 1994 | 466065842.123138 VIETNAM | 1993 | 469551116.529743 VIETNAM | 1992 | 469240312.226312 (175 rows) COMMIT; COMMIT