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 | 268219062.382376 ALGERIA | 1997 | 450496764.452402 ALGERIA | 1996 | 454237332.14123 ALGERIA | 1995 | 455493530.439914 ALGERIA | 1994 | 446263103.025403 ALGERIA | 1993 | 448958772.806775 ALGERIA | 1992 | 450099461.158614 ARGENTINA | 1998 | 278386286.14843 ARGENTINA | 1997 | 474390145.717116 ARGENTINA | 1996 | 477148555.581286 ARGENTINA | 1995 | 475750458.179171 ARGENTINA | 1994 | 472405448.272156 ARGENTINA | 1993 | 477589275.327963 ARGENTINA | 1992 | 471824817.278637 BRAZIL | 1998 | 268637311.014804 BRAZIL | 1997 | 461353020.810057 BRAZIL | 1996 | 461776909.105578 BRAZIL | 1995 | 454192862.442796 BRAZIL | 1994 | 458410632.626319 BRAZIL | 1993 | 452733390.718222 BRAZIL | 1992 | 461018344.78735 CANADA | 1998 | 273739859.298691 CANADA | 1997 | 469489366.99084 CANADA | 1996 | 470198077.380642 CANADA | 1995 | 464834729.111937 CANADA | 1994 | 465072422.891703 CANADA | 1993 | 465433681.674165 CANADA | 1992 | 468726730.955947 CHINA | 1998 | 278262407.058211 CHINA | 1997 | 466532248.695723 CHINA | 1996 | 469695624.346769 CHINA | 1995 | 456548271.156763 CHINA | 1994 | 462827875.436478 CHINA | 1993 | 460817995.241027 CHINA | 1992 | 462269914.992487 EGYPT | 1998 | 274339991.399597 EGYPT | 1997 | 456024535.335689 EGYPT | 1996 | 462437800.226877 EGYPT | 1995 | 465593716.529619 EGYPT | 1994 | 461241481.529734 EGYPT | 1993 | 465992715.944679 EGYPT | 1992 | 465009683.459856 ETHIOPIA | 1998 | 266806148.9546 ETHIOPIA | 1997 | 453569924.792303 ETHIOPIA | 1996 | 459265325.882291 ETHIOPIA | 1995 | 458616271.732412 ETHIOPIA | 1994 | 464218539.254924 ETHIOPIA | 1993 | 460936766.789116 ETHIOPIA | 1992 | 454539364.999135 FRANCE | 1998 | 270751991.464854 FRANCE | 1997 | 458780082.085999 FRANCE | 1996 | 460131387.903054 FRANCE | 1995 | 459827466.541367 FRANCE | 1994 | 456537935.408245 FRANCE | 1993 | 460970480.670462 FRANCE | 1992 | 453921934.564233 GERMANY | 1998 | 278376557.657115 GERMANY | 1997 | 469966018.898972 GERMANY | 1996 | 480488216.902215 GERMANY | 1995 | 475611871.279983 GERMANY | 1994 | 469999102.305502 GERMANY | 1993 | 473443591.087237 GERMANY | 1992 | 470381592.970655 INDIA | 1998 | 268627087.535482 INDIA | 1997 | 469144570.219328 INDIA | 1996 | 478761479.123901 INDIA | 1995 | 465600205.916813 INDIA | 1994 | 468636322.786446 INDIA | 1993 | 474843993.364376 INDIA | 1992 | 461710966.238757 INDONESIA | 1998 | 279088774.82664 INDONESIA | 1997 | 456089437.583454 INDONESIA | 1996 | 466354812.881475 INDONESIA | 1995 | 464303198.747858 INDONESIA | 1994 | 465388115.796661 INDONESIA | 1993 | 461230234.888542 INDONESIA | 1992 | 470082653.129499 IRAN | 1998 | 266808775.504793 IRAN | 1997 | 452738530.488624 IRAN | 1996 | 460057589.089053 IRAN | 1995 | 456816655.27571 IRAN | 1994 | 464440796.198971 IRAN | 1993 | 460104281.599283 IRAN | 1992 | 449729498.705704 IRAQ | 1998 | 280083936.451822 IRAQ | 1997 | 473042509.448221 IRAQ | 1996 | 469519111.697485 IRAQ | 1995 | 467647868.808568 IRAQ | 1994 | 465079667.893079 IRAQ | 1993 | 469148200.678495 IRAQ | 1992 | 477278870.401811 JAPAN | 1998 | 273541714.462165 JAPAN | 1997 | 470306453.529015 JAPAN | 1996 | 465083058.50294 JAPAN | 1995 | 462630983.826009 JAPAN | 1994 | 468973280.744857 JAPAN | 1993 | 465489247.469358 JAPAN | 1992 | 459816031.367442 JORDAN | 1998 | 269083752.35289 JORDAN | 1997 | 459547745.697512 JORDAN | 1996 | 458623622.303734 JORDAN | 1995 | 450607754.01735 JORDAN | 1994 | 454637592.739675 JORDAN | 1993 | 451047276.627625 JORDAN | 1992 | 455871548.234547 KENYA | 1998 | 271200326.418221 KENYA | 1997 | 462692884.360441 KENYA | 1996 | 470310708.110482 KENYA | 1995 | 476013412.720623 KENYA | 1994 | 464741274.617414 KENYA | 1993 | 463270472.68393 KENYA | 1992 | 475096557.580595 MOROCCO | 1998 | 280970726.091949 MOROCCO | 1997 | 467781280.684939 MOROCCO | 1996 | 468390400.70943 MOROCCO | 1995 | 464055108.536848 MOROCCO | 1994 | 468174913.016045 MOROCCO | 1993 | 469012138.050005 MOROCCO | 1992 | 468489487.33891 MOZAMBIQUE | 1998 | 274716948.612713 MOZAMBIQUE | 1997 | 468776168.717678 MOZAMBIQUE | 1996 | 466519427.650661 MOZAMBIQUE | 1995 | 472526433.319153 MOZAMBIQUE | 1994 | 466937407.823784 MOZAMBIQUE | 1993 | 466930112.517571 MOZAMBIQUE | 1992 | 466902896.418186 PERU | 1998 | 275011754.868381 PERU | 1997 | 463784503.334844 PERU | 1996 | 463053144.408993 PERU | 1995 | 459812629.202909 PERU | 1994 | 471055396.742429 PERU | 1993 | 461294552.989031 PERU | 1992 | 467641552.287848 ROMANIA | 1998 | 268846399.425974 ROMANIA | 1997 | 465647707.786616 ROMANIA | 1996 | 468029826.309258 ROMANIA | 1995 | 468981816.12482 ROMANIA | 1994 | 460403903.819972 ROMANIA | 1993 | 463488571.380017 ROMANIA | 1992 | 461263290.803238 RUSSIA | 1998 | 276749232.045233 RUSSIA | 1997 | 470113719.367737 RUSSIA | 1996 | 467660723.243028 RUSSIA | 1995 | 470731948.804056 RUSSIA | 1994 | 469470134.768397 RUSSIA | 1993 | 468794236.075589 RUSSIA | 1992 | 473562021.512312 SAUDI ARABIA | 1998 | 280145912.212186 SAUDI ARABIA | 1997 | 465061404.983002 SAUDI ARABIA | 1996 | 464940423.894138 SAUDI ARABIA | 1995 | 466401406.969893 SAUDI ARABIA | 1994 | 463986931.987395 SAUDI ARABIA | 1993 | 462509806.399931 SAUDI ARABIA | 1992 | 463976465.24338 UNITED KINGDOM | 1998 | 272070812.23196 UNITED KINGDOM | 1997 | 459873748.934557 UNITED KINGDOM | 1996 | 464422399.062577 UNITED KINGDOM | 1995 | 459394782.743326 UNITED KINGDOM | 1994 | 461433037.847037 UNITED KINGDOM | 1993 | 455736167.645427 UNITED KINGDOM | 1992 | 458232111.757359 UNITED STATES | 1998 | 269852064.117762 UNITED STATES | 1997 | 455498669.867657 UNITED STATES | 1996 | 458631359.025971 UNITED STATES | 1995 | 465656891.543142 UNITED STATES | 1994 | 469714508.063056 UNITED STATES | 1993 | 457009869.102573 UNITED STATES | 1992 | 454213408.179143 VIETNAM | 1998 | 273651638.686023 VIETNAM | 1997 | 460348798.986936 VIETNAM | 1996 | 467848014.259084 VIETNAM | 1995 | 464361545.90844 VIETNAM | 1994 | 465713552.607582 VIETNAM | 1993 | 464065330.884231 VIETNAM | 1992 | 462151005.834549 (175 rows) COMMIT; COMMIT