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 '%orchid%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 269590282.545913 ALGERIA | 1997 | 456466622.077606 ALGERIA | 1996 | 461526612.813553 ALGERIA | 1995 | 471476709.960378 ALGERIA | 1994 | 462945858.171509 ALGERIA | 1993 | 461234113.268897 ALGERIA | 1992 | 465298138.263495 ARGENTINA | 1998 | 272700574.198045 ARGENTINA | 1997 | 472204906.597368 ARGENTINA | 1996 | 466611845.782313 ARGENTINA | 1995 | 463318822.22706 ARGENTINA | 1994 | 470822286.520788 ARGENTINA | 1993 | 464729616.552878 ARGENTINA | 1992 | 458981342.52397 BRAZIL | 1998 | 266519963.850783 BRAZIL | 1997 | 471437539.48562 BRAZIL | 1996 | 469498213.061185 BRAZIL | 1995 | 471191285.647923 BRAZIL | 1994 | 466783654.766158 BRAZIL | 1993 | 465797779.847172 BRAZIL | 1992 | 468700841.071834 CANADA | 1998 | 281316453.211466 CANADA | 1997 | 475386034.975503 CANADA | 1996 | 475635640.689667 CANADA | 1995 | 478037716.216579 CANADA | 1994 | 475363145.469134 CANADA | 1993 | 476359882.864014 CANADA | 1992 | 475617872.996256 CHINA | 1998 | 274453499.789036 CHINA | 1997 | 472941624.88245 CHINA | 1996 | 468608083.202498 CHINA | 1995 | 465498490.633419 CHINA | 1994 | 468156559.236528 CHINA | 1993 | 467726767.230277 CHINA | 1992 | 468622269.607249 EGYPT | 1998 | 273842216.530913 EGYPT | 1997 | 460255101.699888 EGYPT | 1996 | 473274411.458743 EGYPT | 1995 | 460833883.732532 EGYPT | 1994 | 463248834.223764 EGYPT | 1993 | 468577317.941123 EGYPT | 1992 | 463153209.154764 ETHIOPIA | 1998 | 265987476.805583 ETHIOPIA | 1997 | 465577675.648486 ETHIOPIA | 1996 | 458450559.399831 ETHIOPIA | 1995 | 460561181.390783 ETHIOPIA | 1994 | 462911524.160986 ETHIOPIA | 1993 | 460995437.228687 ETHIOPIA | 1992 | 466593161.067013 FRANCE | 1998 | 269542623.99728 FRANCE | 1997 | 466706374.432035 FRANCE | 1996 | 461672365.536376 FRANCE | 1995 | 458950600.356838 FRANCE | 1994 | 458927601.521947 FRANCE | 1993 | 458922043.308423 FRANCE | 1992 | 457695026.236804 GERMANY | 1998 | 272207062.286947 GERMANY | 1997 | 476058231.235959 GERMANY | 1996 | 472218880.570619 GERMANY | 1995 | 475573876.306786 GERMANY | 1994 | 471187175.044062 GERMANY | 1993 | 478395469.412174 GERMANY | 1992 | 470603036.991183 INDIA | 1998 | 275645681.860156 INDIA | 1997 | 476730627.246787 INDIA | 1996 | 482905841.381517 INDIA | 1995 | 476891484.549465 INDIA | 1994 | 477939985.044923 INDIA | 1993 | 483271531.9397 INDIA | 1992 | 476985181.718735 INDONESIA | 1998 | 276060838.203013 INDONESIA | 1997 | 464640029.640314 INDONESIA | 1996 | 461574848.753373 INDONESIA | 1995 | 450312866.552771 INDONESIA | 1994 | 459476436.092222 INDONESIA | 1993 | 471655844.163249 INDONESIA | 1992 | 461563180.605999 IRAN | 1998 | 268270676.209376 IRAN | 1997 | 468520098.96792 IRAN | 1996 | 468689468.82313 IRAN | 1995 | 472477965.671069 IRAN | 1994 | 464224147.706906 IRAN | 1993 | 471582180.521955 IRAN | 1992 | 477127490.854302 IRAQ | 1998 | 280425250.43117 IRAQ | 1997 | 471992521.145616 IRAQ | 1996 | 468404328.923885 IRAQ | 1995 | 472258204.871939 IRAQ | 1994 | 474744308.520376 IRAQ | 1993 | 476421929.558675 IRAQ | 1992 | 477295485.152442 JAPAN | 1998 | 269865383.718969 JAPAN | 1997 | 464779917.028328 JAPAN | 1996 | 458268129.678564 JAPAN | 1995 | 463620801.848103 JAPAN | 1994 | 458875357.160706 JAPAN | 1993 | 465486985.038181 JAPAN | 1992 | 472838282.010954 JORDAN | 1998 | 267379456.595649 JORDAN | 1997 | 465694832.388813 JORDAN | 1996 | 460180089.744355 JORDAN | 1995 | 467383685.442569 JORDAN | 1994 | 465140740.24955 JORDAN | 1993 | 465070074.861391 JORDAN | 1992 | 461646620.116211 KENYA | 1998 | 283466826.050719 KENYA | 1997 | 474072678.850662 KENYA | 1996 | 481118968.776241 KENYA | 1995 | 476965077.397548 KENYA | 1994 | 477842258.990502 KENYA | 1993 | 484706736.400596 KENYA | 1992 | 476648689.139065 MOROCCO | 1998 | 277373449.306808 MOROCCO | 1997 | 475168049.343914 MOROCCO | 1996 | 481735069.134911 MOROCCO | 1995 | 471476400.279515 MOROCCO | 1994 | 479039337.993251 MOROCCO | 1993 | 480250458.142286 MOROCCO | 1992 | 476680834.407837 MOZAMBIQUE | 1998 | 268511835.005426 MOZAMBIQUE | 1997 | 468361616.91856 MOZAMBIQUE | 1996 | 470638919.800252 MOZAMBIQUE | 1995 | 465023739.014671 MOZAMBIQUE | 1994 | 459031918.85832 MOZAMBIQUE | 1993 | 467588428.792053 MOZAMBIQUE | 1992 | 459047171.457925 PERU | 1998 | 276294371.388291 PERU | 1997 | 466608439.292035 PERU | 1996 | 474908656.134862 PERU | 1995 | 464424229.104477 PERU | 1994 | 475489705.027903 PERU | 1993 | 477621616.765743 PERU | 1992 | 476394565.499285 ROMANIA | 1998 | 271601858.578816 ROMANIA | 1997 | 470868306.338537 ROMANIA | 1996 | 475583575.821186 ROMANIA | 1995 | 472879942.3215 ROMANIA | 1994 | 466830403.230631 ROMANIA | 1993 | 462517110.388526 ROMANIA | 1992 | 471204481.457507 RUSSIA | 1998 | 276764307.726422 RUSSIA | 1997 | 475199152.652519 RUSSIA | 1996 | 475998963.506195 RUSSIA | 1995 | 468152386.871876 RUSSIA | 1994 | 476562299.817757 RUSSIA | 1993 | 469411947.656684 RUSSIA | 1992 | 468673250.636272 SAUDI ARABIA | 1998 | 274162648.622071 SAUDI ARABIA | 1997 | 463374200.338593 SAUDI ARABIA | 1996 | 468800245.651683 SAUDI ARABIA | 1995 | 469828924.268375 SAUDI ARABIA | 1994 | 472442831.773315 SAUDI ARABIA | 1993 | 474504735.261148 SAUDI ARABIA | 1992 | 467485646.571495 UNITED KINGDOM | 1998 | 269370971.743293 UNITED KINGDOM | 1997 | 454682612.860283 UNITED KINGDOM | 1996 | 461490981.225674 UNITED KINGDOM | 1995 | 459646914.91652 UNITED KINGDOM | 1994 | 463221830.435683 UNITED KINGDOM | 1993 | 463048488.72398 UNITED KINGDOM | 1992 | 467326779.205003 UNITED STATES | 1998 | 280988720.388175 UNITED STATES | 1997 | 479543027.211628 UNITED STATES | 1996 | 473871691.257478 UNITED STATES | 1995 | 477342065.581099 UNITED STATES | 1994 | 475986686.031029 UNITED STATES | 1993 | 475142116.413332 UNITED STATES | 1992 | 485716381.427275 VIETNAM | 1998 | 271405516.789859 VIETNAM | 1997 | 466019009.779122 VIETNAM | 1996 | 471198443.594717 VIETNAM | 1995 | 462625389.678248 VIETNAM | 1994 | 469070831.321568 VIETNAM | 1993 | 466519449.663569 VIETNAM | 1992 | 462103898.635071 (175 rows) COMMIT; COMMIT