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 '%cyan%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 274197331.716632 ALGERIA | 1997 | 461274970.399324 ALGERIA | 1996 | 474045747.173821 ALGERIA | 1995 | 464558728.958014 ALGERIA | 1994 | 472547560.653171 ALGERIA | 1993 | 469137005.767221 ALGERIA | 1992 | 467179633.900131 ARGENTINA | 1998 | 278815563.918179 ARGENTINA | 1997 | 469038105.536562 ARGENTINA | 1996 | 474433034.198938 ARGENTINA | 1995 | 468573862.768517 ARGENTINA | 1994 | 471009196.002873 ARGENTINA | 1993 | 472907185.048049 ARGENTINA | 1992 | 470140668.836822 BRAZIL | 1998 | 278994256.055039 BRAZIL | 1997 | 476665799.34423 BRAZIL | 1996 | 474589649.609258 BRAZIL | 1995 | 478388191.853182 BRAZIL | 1994 | 474915696.487854 BRAZIL | 1993 | 483131547.900645 BRAZIL | 1992 | 487443894.130649 CANADA | 1998 | 276935199.755743 CANADA | 1997 | 477731263.645616 CANADA | 1996 | 481139809.607068 CANADA | 1995 | 479095398.851427 CANADA | 1994 | 490570406.647592 CANADA | 1993 | 474313340.361805 CANADA | 1992 | 483440259.955473 CHINA | 1998 | 265937046.118054 CHINA | 1997 | 470145336.28783 CHINA | 1996 | 462749634.519372 CHINA | 1995 | 469728373.972737 CHINA | 1994 | 459117489.911383 CHINA | 1993 | 467102147.311509 CHINA | 1992 | 466313041.749716 EGYPT | 1998 | 275108810.884113 EGYPT | 1997 | 470588560.007363 EGYPT | 1996 | 471916308.997656 EGYPT | 1995 | 465695618.970212 EGYPT | 1994 | 462720126.542114 EGYPT | 1993 | 468044975.491683 EGYPT | 1992 | 471675809.66835 ETHIOPIA | 1998 | 272211100.214511 ETHIOPIA | 1997 | 455628046.429299 ETHIOPIA | 1996 | 459031249.225134 ETHIOPIA | 1995 | 454405268.27169 ETHIOPIA | 1994 | 457598804.098679 ETHIOPIA | 1993 | 457921237.148783 ETHIOPIA | 1992 | 461191509.805298 FRANCE | 1998 | 275766086.555152 FRANCE | 1997 | 462497012.222744 FRANCE | 1996 | 463869946.769599 FRANCE | 1995 | 465608531.220926 FRANCE | 1994 | 463961972.451663 FRANCE | 1993 | 466595720.823274 FRANCE | 1992 | 455776392.504929 GERMANY | 1998 | 281250453.592883 GERMANY | 1997 | 481016772.012633 GERMANY | 1996 | 481414439.419089 GERMANY | 1995 | 483167507.674174 GERMANY | 1994 | 484793112.762115 GERMANY | 1993 | 478840020.008531 GERMANY | 1992 | 480317505.296198 INDIA | 1998 | 275257145.770121 INDIA | 1997 | 484732528.052727 INDIA | 1996 | 484749767.224007 INDIA | 1995 | 474839522.058341 INDIA | 1994 | 484041276.906846 INDIA | 1993 | 480077671.819056 INDIA | 1992 | 490814630.295448 INDONESIA | 1998 | 276279579.01761 INDONESIA | 1997 | 461587313.761693 INDONESIA | 1996 | 461692087.545838 INDONESIA | 1995 | 459998880.724096 INDONESIA | 1994 | 456963012.325457 INDONESIA | 1993 | 455175368.20007 INDONESIA | 1992 | 465214959.069953 IRAN | 1998 | 276643610.681745 IRAN | 1997 | 476713506.506667 IRAN | 1996 | 472401097.136986 IRAN | 1995 | 470842579.35989 IRAN | 1994 | 468467240.011201 IRAN | 1993 | 472329585.913326 IRAN | 1992 | 473596323.336163 IRAQ | 1998 | 280314997.133993 IRAQ | 1997 | 475843558.886882 IRAQ | 1996 | 471428836.78623 IRAQ | 1995 | 476580034.009399 IRAQ | 1994 | 470215701.272019 IRAQ | 1993 | 465822981.79879 IRAQ | 1992 | 479953253.318298 JAPAN | 1998 | 273976571.860323 JAPAN | 1997 | 466894928.571322 JAPAN | 1996 | 467299039.633298 JAPAN | 1995 | 467255466.258881 JAPAN | 1994 | 470056131.70508 JAPAN | 1993 | 467889935.037709 JAPAN | 1992 | 461431761.13439 JORDAN | 1998 | 272455834.927568 JORDAN | 1997 | 462615060.929413 JORDAN | 1996 | 450855277.844417 JORDAN | 1995 | 462820597.771608 JORDAN | 1994 | 461723052.211626 JORDAN | 1993 | 459214107.477402 JORDAN | 1992 | 466013450.274945 KENYA | 1998 | 274339461.081271 KENYA | 1997 | 474110958.861284 KENYA | 1996 | 472337226.122755 KENYA | 1995 | 478299016.576232 KENYA | 1994 | 480221378.469929 KENYA | 1993 | 481168584.964169 KENYA | 1992 | 475380372.628592 MOROCCO | 1998 | 278484732.886041 MOROCCO | 1997 | 468218681.418403 MOROCCO | 1996 | 463492943.77942 MOROCCO | 1995 | 463086004.0397 MOROCCO | 1994 | 471068763.544764 MOROCCO | 1993 | 464888943.870039 MOROCCO | 1992 | 469757363.269191 MOZAMBIQUE | 1998 | 274779024.317604 MOZAMBIQUE | 1997 | 462021916.247406 MOZAMBIQUE | 1996 | 459795818.005667 MOZAMBIQUE | 1995 | 462954945.983843 MOZAMBIQUE | 1994 | 467828033.818325 MOZAMBIQUE | 1993 | 468965069.486295 MOZAMBIQUE | 1992 | 460925492.578763 PERU | 1998 | 278975641.870979 PERU | 1997 | 478226263.33149 PERU | 1996 | 477573959.119791 PERU | 1995 | 476487109.551933 PERU | 1994 | 474014451.680537 PERU | 1993 | 467981382.517344 PERU | 1992 | 474904866.105794 ROMANIA | 1998 | 275425205.382452 ROMANIA | 1997 | 464543785.353057 ROMANIA | 1996 | 468839224.278007 ROMANIA | 1995 | 471283785.708773 ROMANIA | 1994 | 466982297.215492 ROMANIA | 1993 | 458993064.365304 ROMANIA | 1992 | 477317556.412969 RUSSIA | 1998 | 280798567.380771 RUSSIA | 1997 | 470414307.69711 RUSSIA | 1996 | 480178105.636843 RUSSIA | 1995 | 477738141.154887 RUSSIA | 1994 | 481657713.007032 RUSSIA | 1993 | 470072627.778404 RUSSIA | 1992 | 475848056.993279 SAUDI ARABIA | 1998 | 275394959.294273 SAUDI ARABIA | 1997 | 478490811.858944 SAUDI ARABIA | 1996 | 476259834.922677 SAUDI ARABIA | 1995 | 481056768.561183 SAUDI ARABIA | 1994 | 466225924.104091 SAUDI ARABIA | 1993 | 474737727.643695 SAUDI ARABIA | 1992 | 479940384.79126 UNITED KINGDOM | 1998 | 269930347.718337 UNITED KINGDOM | 1997 | 458813761.193976 UNITED KINGDOM | 1996 | 468834706.559057 UNITED KINGDOM | 1995 | 465348080.073251 UNITED KINGDOM | 1994 | 459582152.82928 UNITED KINGDOM | 1993 | 463150794.51453 UNITED KINGDOM | 1992 | 472023489.493379 UNITED STATES | 1998 | 280007635.902691 UNITED STATES | 1997 | 480373970.54749 UNITED STATES | 1996 | 474906364.375247 UNITED STATES | 1995 | 471983058.511223 UNITED STATES | 1994 | 467707800.746234 UNITED STATES | 1993 | 473224634.034753 UNITED STATES | 1992 | 469322303.566865 VIETNAM | 1998 | 272268343.736357 VIETNAM | 1997 | 472781897.177188 VIETNAM | 1996 | 469112147.426839 VIETNAM | 1995 | 472950954.048395 VIETNAM | 1994 | 469288343.893181 VIETNAM | 1993 | 479447006.526709 VIETNAM | 1992 | 476559157.822978 (175 rows) COMMIT; COMMIT