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.246413 ALGERIA | 1997 | 450474944.694138 ALGERIA | 1996 | 458628251.119836 ALGERIA | 1995 | 462139303.253739 ALGERIA | 1994 | 457726653.577365 ALGERIA | 1993 | 461408680.294385 ALGERIA | 1992 | 461720064.552043 ARGENTINA | 1998 | 271014891.678027 ARGENTINA | 1997 | 470846672.510148 ARGENTINA | 1996 | 461225634.686521 ARGENTINA | 1995 | 461991230.418981 ARGENTINA | 1994 | 469650797.660981 ARGENTINA | 1993 | 466166665.52488 ARGENTINA | 1992 | 471158431.369631 BRAZIL | 1998 | 278439495.110137 BRAZIL | 1997 | 467358119.438828 BRAZIL | 1996 | 468432731.77363 BRAZIL | 1995 | 464425165.490567 BRAZIL | 1994 | 472862579.061973 BRAZIL | 1993 | 477994592.625856 BRAZIL | 1992 | 477070706.984038 CANADA | 1998 | 276915745.861467 CANADA | 1997 | 477378524.892056 CANADA | 1996 | 476289462.491759 CANADA | 1995 | 478880452.040473 CANADA | 1994 | 482977124.414648 CANADA | 1993 | 474774249.314091 CANADA | 1992 | 479243912.977594 CHINA | 1998 | 265795491.653157 CHINA | 1997 | 464873005.26087 CHINA | 1996 | 452595446.938547 CHINA | 1995 | 467131662.418089 CHINA | 1994 | 456301596.087931 CHINA | 1993 | 458548702.048628 CHINA | 1992 | 464478033.511148 EGYPT | 1998 | 270808584.801029 EGYPT | 1997 | 460452456.342761 EGYPT | 1996 | 473264129.269846 EGYPT | 1995 | 457627539.461066 EGYPT | 1994 | 457511007.757764 EGYPT | 1993 | 465896240.007717 EGYPT | 1992 | 461958747.914353 ETHIOPIA | 1998 | 268505241.005371 ETHIOPIA | 1997 | 461779622.28155 ETHIOPIA | 1996 | 451622930.171103 ETHIOPIA | 1995 | 461928782.172549 ETHIOPIA | 1994 | 461622522.17514 ETHIOPIA | 1993 | 446526569.252237 ETHIOPIA | 1992 | 460207074.299255 FRANCE | 1998 | 262400747.456208 FRANCE | 1997 | 449671822.388072 FRANCE | 1996 | 456910330.587692 FRANCE | 1995 | 462079012.931535 FRANCE | 1994 | 459355663.773869 FRANCE | 1993 | 456774757.419926 FRANCE | 1992 | 458469367.567209 GERMANY | 1998 | 278763403.571084 GERMANY | 1997 | 477699361.721293 GERMANY | 1996 | 470474331.860098 GERMANY | 1995 | 478629749.707198 GERMANY | 1994 | 472297708.688091 GERMANY | 1993 | 474486006.308667 GERMANY | 1992 | 475181603.932507 INDIA | 1998 | 270501978.670178 INDIA | 1997 | 473397109.030162 INDIA | 1996 | 472195590.174773 INDIA | 1995 | 472151883.557783 INDIA | 1994 | 477015043.451945 INDIA | 1993 | 472207697.629422 INDIA | 1992 | 473902411.074504 INDONESIA | 1998 | 279574138.395593 INDONESIA | 1997 | 474241085.761992 INDONESIA | 1996 | 463632383.444894 INDONESIA | 1995 | 476651523.741874 INDONESIA | 1994 | 472133011.718637 INDONESIA | 1993 | 474390092.622638 INDONESIA | 1992 | 471272329.01663 IRAN | 1998 | 266208412.993408 IRAN | 1997 | 463258211.815796 IRAN | 1996 | 454900877.872801 IRAN | 1995 | 459128187.742841 IRAN | 1994 | 452643351.830633 IRAN | 1993 | 456574876.314883 IRAN | 1992 | 458492817.246586 IRAQ | 1998 | 273137094.459276 IRAQ | 1997 | 472147554.447199 IRAQ | 1996 | 471004726.807752 IRAQ | 1995 | 461485452.399069 IRAQ | 1994 | 477708603.201955 IRAQ | 1993 | 474750331.359997 IRAQ | 1992 | 469640259.002024 JAPAN | 1998 | 268397850.506978 JAPAN | 1997 | 465329945.132895 JAPAN | 1996 | 468534389.37122 JAPAN | 1995 | 465759400.628615 JAPAN | 1994 | 468820763.360674 JAPAN | 1993 | 468781688.982242 JAPAN | 1992 | 469261684.626359 JORDAN | 1998 | 275255718.654329 JORDAN | 1997 | 467861327.907533 JORDAN | 1996 | 468465816.148242 JORDAN | 1995 | 463229545.246863 JORDAN | 1994 | 463232661.613589 JORDAN | 1993 | 465772760.102707 JORDAN | 1992 | 465362871.90621 KENYA | 1998 | 276656511.270795 KENYA | 1997 | 476754306.693036 KENYA | 1996 | 487283140.769188 KENYA | 1995 | 479451663.48441 KENYA | 1994 | 477835149.049684 KENYA | 1993 | 480618315.962289 KENYA | 1992 | 483627085.582613 MOROCCO | 1998 | 275519318.501467 MOROCCO | 1997 | 464180467.741199 MOROCCO | 1996 | 464794817.08947 MOROCCO | 1995 | 463405409.18134 MOROCCO | 1994 | 468502596.219525 MOROCCO | 1993 | 466720344.202099 MOROCCO | 1992 | 470252306.975119 MOZAMBIQUE | 1998 | 268285589.636586 MOZAMBIQUE | 1997 | 464756515.112877 MOZAMBIQUE | 1996 | 468291768.478797 MOZAMBIQUE | 1995 | 453720140.740081 MOZAMBIQUE | 1994 | 465227238.12075 MOZAMBIQUE | 1993 | 462061475.745411 MOZAMBIQUE | 1992 | 460118269.761253 PERU | 1998 | 271332075.323442 PERU | 1997 | 472263735.498439 PERU | 1996 | 461651434.00674 PERU | 1995 | 468960674.620793 PERU | 1994 | 463315040.816943 PERU | 1993 | 470565460.338685 PERU | 1992 | 474613032.570239 ROMANIA | 1998 | 278722220.076125 ROMANIA | 1997 | 467927363.475695 ROMANIA | 1996 | 475727057.387751 ROMANIA | 1995 | 475454751.18351 ROMANIA | 1994 | 469883853.829797 ROMANIA | 1993 | 472224614.48708 ROMANIA | 1992 | 474805782.24321 RUSSIA | 1998 | 283771803.183342 RUSSIA | 1997 | 485849176.74183 RUSSIA | 1996 | 474566222.203933 RUSSIA | 1995 | 474823470.217165 RUSSIA | 1994 | 485284305.382101 RUSSIA | 1993 | 479961483.176444 RUSSIA | 1992 | 488463176.464637 SAUDI ARABIA | 1998 | 279716792.798749 SAUDI ARABIA | 1997 | 481324571.198166 SAUDI ARABIA | 1996 | 485112475.159332 SAUDI ARABIA | 1995 | 470975785.728385 SAUDI ARABIA | 1994 | 473242652.315091 SAUDI ARABIA | 1993 | 468854088.873666 SAUDI ARABIA | 1992 | 481994505.11755 UNITED KINGDOM | 1998 | 270952636.013797 UNITED KINGDOM | 1997 | 470554466.915581 UNITED KINGDOM | 1996 | 471956534.32116 UNITED KINGDOM | 1995 | 465636511.233048 UNITED KINGDOM | 1994 | 469971123.596552 UNITED KINGDOM | 1993 | 468547036.850372 UNITED KINGDOM | 1992 | 459516189.318184 UNITED STATES | 1998 | 286043411.097931 UNITED STATES | 1997 | 469658296.613561 UNITED STATES | 1996 | 483145334.316187 UNITED STATES | 1995 | 483076645.348584 UNITED STATES | 1994 | 482222333.150782 UNITED STATES | 1993 | 472766162.248652 UNITED STATES | 1992 | 476177254.316266 VIETNAM | 1998 | 273367830.946982 VIETNAM | 1997 | 452515340.048455 VIETNAM | 1996 | 462642261.737996 VIETNAM | 1995 | 464260129.127906 VIETNAM | 1994 | 470080640.8522 VIETNAM | 1993 | 466993827.202403 VIETNAM | 1992 | 463969772.080216 (175 rows) COMMIT; COMMIT