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 '%hot%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263777469.246412 ALGERIA | 1997 | 450474944.694139 ALGERIA | 1996 | 458628251.119837 ALGERIA | 1995 | 462139303.253736 ALGERIA | 1994 | 457726653.577362 ALGERIA | 1993 | 461408680.294389 ALGERIA | 1992 | 461720064.55204 ARGENTINA | 1998 | 271014891.678026 ARGENTINA | 1997 | 470846672.51015 ARGENTINA | 1996 | 461225634.686519 ARGENTINA | 1995 | 461991230.418982 ARGENTINA | 1994 | 469650797.66098 ARGENTINA | 1993 | 466166665.524887 ARGENTINA | 1992 | 471158431.369632 BRAZIL | 1998 | 278439495.110138 BRAZIL | 1997 | 467358119.438824 BRAZIL | 1996 | 468432731.773633 BRAZIL | 1995 | 464425165.490566 BRAZIL | 1994 | 472862579.061972 BRAZIL | 1993 | 477994592.625855 BRAZIL | 1992 | 477070706.98404 CANADA | 1998 | 276915745.861468 CANADA | 1997 | 477378524.892055 CANADA | 1996 | 476289462.491758 CANADA | 1995 | 478880452.040473 CANADA | 1994 | 482977124.414644 CANADA | 1993 | 474774249.314089 CANADA | 1992 | 479243912.977597 CHINA | 1998 | 265795491.653158 CHINA | 1997 | 464873005.260869 CHINA | 1996 | 452595446.938546 CHINA | 1995 | 467131662.418091 CHINA | 1994 | 456301596.087929 CHINA | 1993 | 458548702.048627 CHINA | 1992 | 464478033.511145 EGYPT | 1998 | 270808584.80103 EGYPT | 1997 | 460452456.342762 EGYPT | 1996 | 473264129.269846 EGYPT | 1995 | 457627539.461069 EGYPT | 1994 | 457511007.757763 EGYPT | 1993 | 465896240.007715 EGYPT | 1992 | 461958747.91435 ETHIOPIA | 1998 | 268505241.005372 ETHIOPIA | 1997 | 461779622.281546 ETHIOPIA | 1996 | 451622930.171102 ETHIOPIA | 1995 | 461928782.172548 ETHIOPIA | 1994 | 461622522.175144 ETHIOPIA | 1993 | 446526569.252238 ETHIOPIA | 1992 | 460207074.299256 FRANCE | 1998 | 262400747.456208 FRANCE | 1997 | 449671822.388069 FRANCE | 1996 | 456910330.58769 FRANCE | 1995 | 462079012.931535 FRANCE | 1994 | 459355663.773867 FRANCE | 1993 | 456774757.419927 FRANCE | 1992 | 458469367.567211 GERMANY | 1998 | 278763403.571084 GERMANY | 1997 | 477699361.721295 GERMANY | 1996 | 470474331.860097 GERMANY | 1995 | 478629749.7072 GERMANY | 1994 | 472297708.688092 GERMANY | 1993 | 474486006.308669 GERMANY | 1992 | 475181603.932506 INDIA | 1998 | 270501978.670179 INDIA | 1997 | 473397109.030163 INDIA | 1996 | 472195590.17477 INDIA | 1995 | 472151883.557783 INDIA | 1994 | 477015043.451948 INDIA | 1993 | 472207697.629421 INDIA | 1992 | 473902411.074504 INDONESIA | 1998 | 279574138.395592 INDONESIA | 1997 | 474241085.76199 INDONESIA | 1996 | 463632383.444895 INDONESIA | 1995 | 476651523.741874 INDONESIA | 1994 | 472133011.718635 INDONESIA | 1993 | 474390092.622639 INDONESIA | 1992 | 471272329.016629 IRAN | 1998 | 266208412.993408 IRAN | 1997 | 463258211.815797 IRAN | 1996 | 454900877.872802 IRAN | 1995 | 459128187.742839 IRAN | 1994 | 452643351.83063 IRAN | 1993 | 456574876.314885 IRAN | 1992 | 458492817.246584 IRAQ | 1998 | 273137094.459277 IRAQ | 1997 | 472147554.447195 IRAQ | 1996 | 471004726.80775 IRAQ | 1995 | 461485452.399069 IRAQ | 1994 | 477708603.201955 IRAQ | 1993 | 474750331.359998 IRAQ | 1992 | 469640259.002025 JAPAN | 1998 | 268397850.506977 JAPAN | 1997 | 465329945.132895 JAPAN | 1996 | 468534389.371219 JAPAN | 1995 | 465759400.628614 JAPAN | 1994 | 468820763.360674 JAPAN | 1993 | 468781688.982242 JAPAN | 1992 | 469261684.626357 JORDAN | 1998 | 275255718.654328 JORDAN | 1997 | 467861327.907536 JORDAN | 1996 | 468465816.148241 JORDAN | 1995 | 463229545.246867 JORDAN | 1994 | 463232661.613587 JORDAN | 1993 | 465772760.10271 JORDAN | 1992 | 465362871.906211 KENYA | 1998 | 276656511.270797 KENYA | 1997 | 476754306.693036 KENYA | 1996 | 487283140.769186 KENYA | 1995 | 479451663.484406 KENYA | 1994 | 477835149.049685 KENYA | 1993 | 480618315.962288 KENYA | 1992 | 483627085.582609 MOROCCO | 1998 | 275519318.501467 MOROCCO | 1997 | 464180467.741201 MOROCCO | 1996 | 464794817.089476 MOROCCO | 1995 | 463405409.181339 MOROCCO | 1994 | 468502596.219526 MOROCCO | 1993 | 466720344.202098 MOROCCO | 1992 | 470252306.975116 MOZAMBIQUE | 1998 | 268285589.636588 MOZAMBIQUE | 1997 | 464756515.112875 MOZAMBIQUE | 1996 | 468291768.478798 MOZAMBIQUE | 1995 | 453720140.740082 MOZAMBIQUE | 1994 | 465227238.120755 MOZAMBIQUE | 1993 | 462061475.745412 MOZAMBIQUE | 1992 | 460118269.761252 PERU | 1998 | 271332075.323443 PERU | 1997 | 472263735.498436 PERU | 1996 | 461651434.006739 PERU | 1995 | 468960674.620792 PERU | 1994 | 463315040.816943 PERU | 1993 | 470565460.338688 PERU | 1992 | 474613032.570242 ROMANIA | 1998 | 278722220.076125 ROMANIA | 1997 | 467927363.475696 ROMANIA | 1996 | 475727057.387751 ROMANIA | 1995 | 475454751.183511 ROMANIA | 1994 | 469883853.829796 ROMANIA | 1993 | 472224614.487081 ROMANIA | 1992 | 474805782.243211 RUSSIA | 1998 | 283771803.183343 RUSSIA | 1997 | 485849176.741827 RUSSIA | 1996 | 474566222.203933 RUSSIA | 1995 | 474823470.217165 RUSSIA | 1994 | 485284305.382104 RUSSIA | 1993 | 479961483.176442 RUSSIA | 1992 | 488463176.46464 SAUDI ARABIA | 1998 | 279716792.79875 SAUDI ARABIA | 1997 | 481324571.198165 SAUDI ARABIA | 1996 | 485112475.159334 SAUDI ARABIA | 1995 | 470975785.728385 SAUDI ARABIA | 1994 | 473242652.31509 SAUDI ARABIA | 1993 | 468854088.873666 SAUDI ARABIA | 1992 | 481994505.11755 UNITED KINGDOM | 1998 | 270952636.013797 UNITED KINGDOM | 1997 | 470554466.91558 UNITED KINGDOM | 1996 | 471956534.321158 UNITED KINGDOM | 1995 | 465636511.233047 UNITED KINGDOM | 1994 | 469971123.596554 UNITED KINGDOM | 1993 | 468547036.850369 UNITED KINGDOM | 1992 | 459516189.318185 UNITED STATES | 1998 | 286043411.097931 UNITED STATES | 1997 | 469658296.613557 UNITED STATES | 1996 | 483145334.316185 UNITED STATES | 1995 | 483076645.348589 UNITED STATES | 1994 | 482222333.150782 UNITED STATES | 1993 | 472766162.248655 UNITED STATES | 1992 | 476177254.316267 VIETNAM | 1998 | 273367830.946982 VIETNAM | 1997 | 452515340.048453 VIETNAM | 1996 | 462642261.737995 VIETNAM | 1995 | 464260129.127908 VIETNAM | 1994 | 470080640.852198 VIETNAM | 1993 | 466993827.202405 VIETNAM | 1992 | 463969772.080216 (175 rows) COMMIT; COMMIT