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 '%yellow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270801396.562363 ALGERIA | 1997 | 471708249.685833 ALGERIA | 1996 | 465870207.25924 ALGERIA | 1995 | 465576717.583218 ALGERIA | 1994 | 465077571.338315 ALGERIA | 1993 | 465235726.073049 ALGERIA | 1992 | 465052798.278149 ARGENTINA | 1998 | 275799962.775772 ARGENTINA | 1997 | 471752250.156976 ARGENTINA | 1996 | 478812759.358898 ARGENTINA | 1995 | 473471909.492385 ARGENTINA | 1994 | 469253960.363349 ARGENTINA | 1993 | 467214572.285789 ARGENTINA | 1992 | 474742128.928845 BRAZIL | 1998 | 280212916.751544 BRAZIL | 1997 | 457356592.28906 BRAZIL | 1996 | 464234926.36869 BRAZIL | 1995 | 460076604.34661 BRAZIL | 1994 | 467764565.592406 BRAZIL | 1993 | 461927852.974207 BRAZIL | 1992 | 469138164.178178 CANADA | 1998 | 271171273.562005 CANADA | 1997 | 484110713.974366 CANADA | 1996 | 478654028.348956 CANADA | 1995 | 477282125.855404 CANADA | 1994 | 481548595.631379 CANADA | 1993 | 475272445.182768 CANADA | 1992 | 468737565.195593 CHINA | 1998 | 267926125.310913 CHINA | 1997 | 454656022.500384 CHINA | 1996 | 465624530.520741 CHINA | 1995 | 462760339.356244 CHINA | 1994 | 454676501.777946 CHINA | 1993 | 455702094.122162 CHINA | 1992 | 461912152.601544 EGYPT | 1998 | 270411912.521023 EGYPT | 1997 | 467956378.374493 EGYPT | 1996 | 472880281.597193 EGYPT | 1995 | 460151558.155279 EGYPT | 1994 | 463605302.379373 EGYPT | 1993 | 470758070.856815 EGYPT | 1992 | 469693578.575382 ETHIOPIA | 1998 | 274947208.838528 ETHIOPIA | 1997 | 459579296.543292 ETHIOPIA | 1996 | 462471429.906415 ETHIOPIA | 1995 | 461090584.342406 ETHIOPIA | 1994 | 460508192.886979 ETHIOPIA | 1993 | 467649562.906464 ETHIOPIA | 1992 | 462444720.272671 FRANCE | 1998 | 270618913.765215 FRANCE | 1997 | 462778950.696201 FRANCE | 1996 | 461499341.044308 FRANCE | 1995 | 465903859.813877 FRANCE | 1994 | 464677986.377797 FRANCE | 1993 | 458998338.299342 FRANCE | 1992 | 461939621.843603 GERMANY | 1998 | 278006384.353296 GERMANY | 1997 | 476735321.35756 GERMANY | 1996 | 479369835.113 GERMANY | 1995 | 484882898.842662 GERMANY | 1994 | 481751783.161451 GERMANY | 1993 | 489874521.754105 GERMANY | 1992 | 475033364.537443 INDIA | 1998 | 280286965.761252 INDIA | 1997 | 467207120.849069 INDIA | 1996 | 469118705.413491 INDIA | 1995 | 469515690.829497 INDIA | 1994 | 472819483.727722 INDIA | 1993 | 470824868.709598 INDIA | 1992 | 461134526.582372 INDONESIA | 1998 | 272724659.722867 INDONESIA | 1997 | 460771207.161127 INDONESIA | 1996 | 454384780.897078 INDONESIA | 1995 | 459034590.994985 INDONESIA | 1994 | 469111313.471983 INDONESIA | 1993 | 464827447.452667 INDONESIA | 1992 | 459708821.723934 IRAN | 1998 | 271343688.909707 IRAN | 1997 | 460296879.160369 IRAN | 1996 | 461751701.645333 IRAN | 1995 | 469392819.385495 IRAN | 1994 | 464456492.235949 IRAN | 1993 | 461115391.659351 IRAN | 1992 | 457624395.644763 IRAQ | 1998 | 278389930.622751 IRAQ | 1997 | 477477352.742226 IRAQ | 1996 | 480949508.953141 IRAQ | 1995 | 483411171.804391 IRAQ | 1994 | 485114947.534926 IRAQ | 1993 | 472442048.271007 IRAQ | 1992 | 476275268.70553 JAPAN | 1998 | 276776594.856979 JAPAN | 1997 | 463718352.656799 JAPAN | 1996 | 472848526.756682 JAPAN | 1995 | 468233334.271335 JAPAN | 1994 | 468655146.188969 JAPAN | 1993 | 472973623.78544 JAPAN | 1992 | 474372445.985861 JORDAN | 1998 | 267113851.992706 JORDAN | 1997 | 454216138.986042 JORDAN | 1996 | 452467439.966432 JORDAN | 1995 | 455910849.360377 JORDAN | 1994 | 464300080.400263 JORDAN | 1993 | 458715802.846823 JORDAN | 1992 | 463179898.177617 KENYA | 1998 | 281151610.509431 KENYA | 1997 | 471215037.900615 KENYA | 1996 | 473003263.534858 KENYA | 1995 | 470856269.977973 KENYA | 1994 | 477055498.217241 KENYA | 1993 | 480110985.017643 KENYA | 1992 | 478016798.173034 MOROCCO | 1998 | 271288186.237248 MOROCCO | 1997 | 462773530.667309 MOROCCO | 1996 | 470001541.561037 MOROCCO | 1995 | 465935028.565487 MOROCCO | 1994 | 465325336.635618 MOROCCO | 1993 | 463370887.73674 MOROCCO | 1992 | 463090456.326236 MOZAMBIQUE | 1998 | 271808069.768755 MOZAMBIQUE | 1997 | 458135298.232065 MOZAMBIQUE | 1996 | 462198032.340434 MOZAMBIQUE | 1995 | 464118866.462561 MOZAMBIQUE | 1994 | 464185184.709932 MOZAMBIQUE | 1993 | 455338433.694104 MOZAMBIQUE | 1992 | 456735561.911699 PERU | 1998 | 276519995.013885 PERU | 1997 | 467377246.333803 PERU | 1996 | 460507377.979826 PERU | 1995 | 467088898.18986 PERU | 1994 | 455576337.215301 PERU | 1993 | 458985571.849615 PERU | 1992 | 467674034.703293 ROMANIA | 1998 | 274127398.00185 ROMANIA | 1997 | 471679329.873574 ROMANIA | 1996 | 472834203.202817 ROMANIA | 1995 | 467532749.120044 ROMANIA | 1994 | 461835385.597635 ROMANIA | 1993 | 474051220.542629 ROMANIA | 1992 | 477665377.214993 RUSSIA | 1998 | 276827395.924936 RUSSIA | 1997 | 470584758.566805 RUSSIA | 1996 | 485088715.31863 RUSSIA | 1995 | 476573254.806942 RUSSIA | 1994 | 475203063.789296 RUSSIA | 1993 | 475914088.132132 RUSSIA | 1992 | 480545680.705114 SAUDI ARABIA | 1998 | 269585252.939966 SAUDI ARABIA | 1997 | 460901838.027213 SAUDI ARABIA | 1996 | 466138027.306555 SAUDI ARABIA | 1995 | 461503939.17229 SAUDI ARABIA | 1994 | 460050751.378493 SAUDI ARABIA | 1993 | 463410121.362345 SAUDI ARABIA | 1992 | 468535014.3954 UNITED KINGDOM | 1998 | 270671318.629609 UNITED KINGDOM | 1997 | 463673081.713686 UNITED KINGDOM | 1996 | 456571612.864721 UNITED KINGDOM | 1995 | 461351746.927417 UNITED KINGDOM | 1994 | 458969533.257289 UNITED KINGDOM | 1993 | 461824210.091026 UNITED KINGDOM | 1992 | 464980456.312567 UNITED STATES | 1998 | 277538339.732835 UNITED STATES | 1997 | 476842892.232263 UNITED STATES | 1996 | 474663670.319721 UNITED STATES | 1995 | 476899861.327179 UNITED STATES | 1994 | 476583083.336596 UNITED STATES | 1993 | 473112228.594876 UNITED STATES | 1992 | 476085798.338808 VIETNAM | 1998 | 271002613.209732 VIETNAM | 1997 | 455200760.556001 VIETNAM | 1996 | 469648619.156398 VIETNAM | 1995 | 465898807.293195 VIETNAM | 1994 | 460697066.74198 VIETNAM | 1993 | 464123809.081126 VIETNAM | 1992 | 460773819.296843 (175 rows) COMMIT; COMMIT