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 '%coral%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 271540954.268603 ALGERIA | 1997 | 462230319.707711 ALGERIA | 1996 | 470457760.436227 ALGERIA | 1995 | 469958673.520822 ALGERIA | 1994 | 467599238.166228 ALGERIA | 1993 | 469823100.727759 ALGERIA | 1992 | 457297827.569925 ARGENTINA | 1998 | 275797777.419353 ARGENTINA | 1997 | 459499121.63166 ARGENTINA | 1996 | 469982018.66205 ARGENTINA | 1995 | 467332895.453515 ARGENTINA | 1994 | 470441581.212315 ARGENTINA | 1993 | 472096168.19142 ARGENTINA | 1992 | 469045555.004296 BRAZIL | 1998 | 275216359.213478 BRAZIL | 1997 | 467775025.298894 BRAZIL | 1996 | 471365274.565827 BRAZIL | 1995 | 468363194.418723 BRAZIL | 1994 | 462613184.562063 BRAZIL | 1993 | 467627102.521997 BRAZIL | 1992 | 459395668.426527 CANADA | 1998 | 280695604.631503 CANADA | 1997 | 470235257.733534 CANADA | 1996 | 473172806.014465 CANADA | 1995 | 474969223.670554 CANADA | 1994 | 483833006.586658 CANADA | 1993 | 471455624.186645 CANADA | 1992 | 472394903.978662 CHINA | 1998 | 272631067.583556 CHINA | 1997 | 461006926.66705 CHINA | 1996 | 456416270.467354 CHINA | 1995 | 459828640.041847 CHINA | 1994 | 453960233.555882 CHINA | 1993 | 454323878.483442 CHINA | 1992 | 470564368.001356 EGYPT | 1998 | 277303366.322931 EGYPT | 1997 | 466689281.352926 EGYPT | 1996 | 477032567.520188 EGYPT | 1995 | 464043631.303041 EGYPT | 1994 | 464921896.629434 EGYPT | 1993 | 471009869.511572 EGYPT | 1992 | 465380922.09484 ETHIOPIA | 1998 | 258701895.673999 ETHIOPIA | 1997 | 453604434.72239 ETHIOPIA | 1996 | 454358914.154631 ETHIOPIA | 1995 | 461526813.058107 ETHIOPIA | 1994 | 457821297.239525 ETHIOPIA | 1993 | 451746786.976264 ETHIOPIA | 1992 | 461559181.948298 FRANCE | 1998 | 269094129.337527 FRANCE | 1997 | 460709866.307673 FRANCE | 1996 | 456966890.908269 FRANCE | 1995 | 461203337.742597 FRANCE | 1994 | 461292277.303463 FRANCE | 1993 | 462825856.989823 FRANCE | 1992 | 456062612.804687 GERMANY | 1998 | 272302282.467426 GERMANY | 1997 | 476220457.826655 GERMANY | 1996 | 463874213.208345 GERMANY | 1995 | 468827987.498779 GERMANY | 1994 | 475712482.555905 GERMANY | 1993 | 477752203.992799 GERMANY | 1992 | 467751028.398125 INDIA | 1998 | 278135885.383155 INDIA | 1997 | 478339473.627566 INDIA | 1996 | 479615154.022307 INDIA | 1995 | 479460844.104147 INDIA | 1994 | 475811047.768234 INDIA | 1993 | 478327353.604065 INDIA | 1992 | 476219787.194073 INDONESIA | 1998 | 270130501.604324 INDONESIA | 1997 | 462251395.060362 INDONESIA | 1996 | 466640711.629491 INDONESIA | 1995 | 466326634.234423 INDONESIA | 1994 | 464514825.209471 INDONESIA | 1993 | 462026630.357009 INDONESIA | 1992 | 471183301.497545 IRAN | 1998 | 279132166.775185 IRAN | 1997 | 475969075.291511 IRAN | 1996 | 473871839.059519 IRAN | 1995 | 468463518.864072 IRAN | 1994 | 464171904.122263 IRAN | 1993 | 464778305.16543 IRAN | 1992 | 468575886.354421 IRAQ | 1998 | 283524124.169394 IRAQ | 1997 | 472062001.978583 IRAQ | 1996 | 472378436.043335 IRAQ | 1995 | 474205572.220218 IRAQ | 1994 | 473012583.555599 IRAQ | 1993 | 470917413.851958 IRAQ | 1992 | 469280006.071911 JAPAN | 1998 | 271429523.809915 JAPAN | 1997 | 467778097.48015 JAPAN | 1996 | 468069728.824575 JAPAN | 1995 | 463932102.536493 JAPAN | 1994 | 474634404.89338 JAPAN | 1993 | 462888031.704724 JAPAN | 1992 | 461599912.290347 JORDAN | 1998 | 268709229.745714 JORDAN | 1997 | 460202134.944153 JORDAN | 1996 | 450433879.155651 JORDAN | 1995 | 455774827.727851 JORDAN | 1994 | 464863251.238458 JORDAN | 1993 | 467153991.191055 JORDAN | 1992 | 460202056.741425 KENYA | 1998 | 277135596.065316 KENYA | 1997 | 477014537.737776 KENYA | 1996 | 468668047.225729 KENYA | 1995 | 480434577.930419 KENYA | 1994 | 468604564.59126 KENYA | 1993 | 474711482.82595 KENYA | 1992 | 478041122.726837 MOROCCO | 1998 | 278855956.018455 MOROCCO | 1997 | 476753797.283601 MOROCCO | 1996 | 472650151.057401 MOROCCO | 1995 | 465529664.973761 MOROCCO | 1994 | 461928128.360466 MOROCCO | 1993 | 472108856.922921 MOROCCO | 1992 | 477652367.164193 MOZAMBIQUE | 1998 | 268818022.853623 MOZAMBIQUE | 1997 | 465431940.754431 MOZAMBIQUE | 1996 | 463529441.475748 MOZAMBIQUE | 1995 | 465930021.008022 MOZAMBIQUE | 1994 | 461669953.874272 MOZAMBIQUE | 1993 | 455144004.338146 MOZAMBIQUE | 1992 | 464833228.628938 PERU | 1998 | 277825622.883599 PERU | 1997 | 474136933.295629 PERU | 1996 | 468221382.957651 PERU | 1995 | 470642880.267931 PERU | 1994 | 466516855.03355 PERU | 1993 | 467703995.246617 PERU | 1992 | 468110073.841365 ROMANIA | 1998 | 272407814.16702 ROMANIA | 1997 | 456915344.685271 ROMANIA | 1996 | 471171981.393661 ROMANIA | 1995 | 460264886.6263 ROMANIA | 1994 | 461862947.421351 ROMANIA | 1993 | 453615948.489119 ROMANIA | 1992 | 462974634.185304 RUSSIA | 1998 | 282494745.83737 RUSSIA | 1997 | 472778493.846692 RUSSIA | 1996 | 477666898.313999 RUSSIA | 1995 | 474106507.109542 RUSSIA | 1994 | 470249999.561407 RUSSIA | 1993 | 469062108.7224 RUSSIA | 1992 | 480894208.013868 SAUDI ARABIA | 1998 | 276473466.160668 SAUDI ARABIA | 1997 | 469525493.094881 SAUDI ARABIA | 1996 | 475095652.482537 SAUDI ARABIA | 1995 | 480689576.331378 SAUDI ARABIA | 1994 | 472570753.849646 SAUDI ARABIA | 1993 | 471300130.43172 SAUDI ARABIA | 1992 | 469085499.439901 UNITED KINGDOM | 1998 | 276972807.609939 UNITED KINGDOM | 1997 | 463865869.285752 UNITED KINGDOM | 1996 | 462051095.231999 UNITED KINGDOM | 1995 | 465594778.554163 UNITED KINGDOM | 1994 | 461254394.699634 UNITED KINGDOM | 1993 | 463629757.728303 UNITED KINGDOM | 1992 | 462230959.712134 UNITED STATES | 1998 | 279626355.601276 UNITED STATES | 1997 | 471043454.05186 UNITED STATES | 1996 | 485000228.575236 UNITED STATES | 1995 | 481564694.881114 UNITED STATES | 1994 | 481371766.983325 UNITED STATES | 1993 | 472932350.355561 UNITED STATES | 1992 | 477573257.578133 VIETNAM | 1998 | 268954189.134433 VIETNAM | 1997 | 455159622.278602 VIETNAM | 1996 | 460657703.319831 VIETNAM | 1995 | 461318065.610706 VIETNAM | 1994 | 459349583.776517 VIETNAM | 1993 | 465377269.309213 VIETNAM | 1992 | 456499603.863791 (175 rows) COMMIT; COMMIT