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 '%drab%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263737913.662113 ALGERIA | 1997 | 456164844.932027 ALGERIA | 1996 | 449118701.750416 ALGERIA | 1995 | 456029275.597324 ALGERIA | 1994 | 460350282.147733 ALGERIA | 1993 | 460964702.5699 ALGERIA | 1992 | 458573962.306178 ARGENTINA | 1998 | 277444631.788445 ARGENTINA | 1997 | 464602911.2171 ARGENTINA | 1996 | 477151518.937604 ARGENTINA | 1995 | 464974921.37847 ARGENTINA | 1994 | 469298426.131913 ARGENTINA | 1993 | 481292276.280973 ARGENTINA | 1992 | 471390280.089826 BRAZIL | 1998 | 273936335.983741 BRAZIL | 1997 | 464818904.532416 BRAZIL | 1996 | 472575020.864046 BRAZIL | 1995 | 464624479.061311 BRAZIL | 1994 | 467486930.834233 BRAZIL | 1993 | 467698139.381364 BRAZIL | 1992 | 470100257.806519 CANADA | 1998 | 281013351.850034 CANADA | 1997 | 479147031.06465 CANADA | 1996 | 487421591.520351 CANADA | 1995 | 484986382.296101 CANADA | 1994 | 488551203.428814 CANADA | 1993 | 478882835.125578 CANADA | 1992 | 478905521.948991 CHINA | 1998 | 278203373.903637 CHINA | 1997 | 475705389.531186 CHINA | 1996 | 476521454.05812 CHINA | 1995 | 476385001.09534 CHINA | 1994 | 463879085.17681 CHINA | 1993 | 472319023.283705 CHINA | 1992 | 474085373.978007 EGYPT | 1998 | 278691775.599724 EGYPT | 1997 | 470207127.624246 EGYPT | 1996 | 472375820.992615 EGYPT | 1995 | 476891893.143073 EGYPT | 1994 | 472215815.866794 EGYPT | 1993 | 473422807.670824 EGYPT | 1992 | 471382318.109347 ETHIOPIA | 1998 | 280526997.068264 ETHIOPIA | 1997 | 470970766.371545 ETHIOPIA | 1996 | 466197497.405979 ETHIOPIA | 1995 | 475492239.390148 ETHIOPIA | 1994 | 473956798.695644 ETHIOPIA | 1993 | 474902481.76594 ETHIOPIA | 1992 | 478725453.668044 FRANCE | 1998 | 272633736.161146 FRANCE | 1997 | 465836192.408035 FRANCE | 1996 | 466556590.743754 FRANCE | 1995 | 468779924.455954 FRANCE | 1994 | 467395595.77009 FRANCE | 1993 | 465241848.634789 FRANCE | 1992 | 465317505.711986 GERMANY | 1998 | 280766533.620157 GERMANY | 1997 | 469827308.769699 GERMANY | 1996 | 473012387.905003 GERMANY | 1995 | 471518288.710863 GERMANY | 1994 | 484888427.468003 GERMANY | 1993 | 476019927.729743 GERMANY | 1992 | 478057126.357055 INDIA | 1998 | 279805958.885623 INDIA | 1997 | 473439178.232669 INDIA | 1996 | 479504234.956942 INDIA | 1995 | 474013511.324174 INDIA | 1994 | 477067260.916085 INDIA | 1993 | 473575257.994259 INDIA | 1992 | 480077294.964864 INDONESIA | 1998 | 276036913.542778 INDONESIA | 1997 | 463202171.266723 INDONESIA | 1996 | 478353329.282621 INDONESIA | 1995 | 475379747.986051 INDONESIA | 1994 | 463310927.070485 INDONESIA | 1993 | 468919779.104253 INDONESIA | 1992 | 466358754.026039 IRAN | 1998 | 274799650.015414 IRAN | 1997 | 468816787.894706 IRAN | 1996 | 471461648.2081 IRAN | 1995 | 475542768.722504 IRAN | 1994 | 470911716.724345 IRAN | 1993 | 479348000.37305 IRAN | 1992 | 478505242.961449 IRAQ | 1998 | 280181058.249597 IRAQ | 1997 | 469298395.225302 IRAQ | 1996 | 476055373.727139 IRAQ | 1995 | 478240269.520371 IRAQ | 1994 | 479671293.463602 IRAQ | 1993 | 479626840.861923 IRAQ | 1992 | 487298281.791563 JAPAN | 1998 | 271303124.537676 JAPAN | 1997 | 459917970.992707 JAPAN | 1996 | 467796823.727561 JAPAN | 1995 | 464079941.491086 JAPAN | 1994 | 467193267.45961 JAPAN | 1993 | 469461556.951424 JAPAN | 1992 | 464650995.177338 JORDAN | 1998 | 268475888.206469 JORDAN | 1997 | 461323684.568377 JORDAN | 1996 | 458862622.590966 JORDAN | 1995 | 454250528.724091 JORDAN | 1994 | 461935772.894227 JORDAN | 1993 | 455981248.418019 JORDAN | 1992 | 464655321.567742 KENYA | 1998 | 271203012.194853 KENYA | 1997 | 466260961.317353 KENYA | 1996 | 474033099.64576 KENYA | 1995 | 468137421.150528 KENYA | 1994 | 471329308.713022 KENYA | 1993 | 461999967.448574 KENYA | 1992 | 472478530.843097 MOROCCO | 1998 | 277051788.552006 MOROCCO | 1997 | 480167314.907763 MOROCCO | 1996 | 471865887.938167 MOROCCO | 1995 | 477319073.983268 MOROCCO | 1994 | 467994644.402903 MOROCCO | 1993 | 469297904.386528 MOROCCO | 1992 | 480186887.746246 MOZAMBIQUE | 1998 | 270870984.43843 MOZAMBIQUE | 1997 | 461556495.075653 MOZAMBIQUE | 1996 | 463072260.922272 MOZAMBIQUE | 1995 | 463850052.584657 MOZAMBIQUE | 1994 | 478815585.163722 MOZAMBIQUE | 1993 | 466375660.397548 MOZAMBIQUE | 1992 | 462343285.466071 PERU | 1998 | 275490842.308688 PERU | 1997 | 472481099.627233 PERU | 1996 | 465075051.86286 PERU | 1995 | 472048967.768442 PERU | 1994 | 471480005.494939 PERU | 1993 | 467555814.220145 PERU | 1992 | 470032808.081022 ROMANIA | 1998 | 269889389.133125 ROMANIA | 1997 | 462950148.319352 ROMANIA | 1996 | 469666828.441006 ROMANIA | 1995 | 463150645.830613 ROMANIA | 1994 | 467608532.291748 ROMANIA | 1993 | 458957926.889318 ROMANIA | 1992 | 467711558.931116 RUSSIA | 1998 | 280552828.183197 RUSSIA | 1997 | 475699303.452981 RUSSIA | 1996 | 474469883.886573 RUSSIA | 1995 | 468835946.641555 RUSSIA | 1994 | 468855001.377926 RUSSIA | 1993 | 463424804.487557 RUSSIA | 1992 | 466503627.181329 SAUDI ARABIA | 1998 | 281350783.845619 SAUDI ARABIA | 1997 | 470721826.306227 SAUDI ARABIA | 1996 | 469162451.508584 SAUDI ARABIA | 1995 | 471830070.148869 SAUDI ARABIA | 1994 | 465187350.579412 SAUDI ARABIA | 1993 | 469651481.770999 SAUDI ARABIA | 1992 | 470667313.20589 UNITED KINGDOM | 1998 | 276263740.697617 UNITED KINGDOM | 1997 | 462594057.571271 UNITED KINGDOM | 1996 | 464862489.342118 UNITED KINGDOM | 1995 | 463581561.037768 UNITED KINGDOM | 1994 | 469299250.851145 UNITED KINGDOM | 1993 | 465632447.242156 UNITED KINGDOM | 1992 | 467419099.141982 UNITED STATES | 1998 | 277559372.792529 UNITED STATES | 1997 | 470683240.951586 UNITED STATES | 1996 | 474034857.636803 UNITED STATES | 1995 | 465846234.075528 UNITED STATES | 1994 | 465517792.749925 UNITED STATES | 1993 | 470390028.4644 UNITED STATES | 1992 | 477045583.042478 VIETNAM | 1998 | 275017287.699227 VIETNAM | 1997 | 463860687.229192 VIETNAM | 1996 | 465189755.57961 VIETNAM | 1995 | 463594299.430391 VIETNAM | 1994 | 463973020.790461 VIETNAM | 1993 | 456823536.44323 VIETNAM | 1992 | 463146854.259724 (175 rows) COMMIT; COMMIT