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 '%frosted%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267399580.673438 ALGERIA | 1997 | 453429726.479187 ALGERIA | 1996 | 449149752.70193 ALGERIA | 1995 | 450025446.94115 ALGERIA | 1994 | 452634482.485395 ALGERIA | 1993 | 445982777.584872 ALGERIA | 1992 | 449489419.169242 ARGENTINA | 1998 | 279651553.263706 ARGENTINA | 1997 | 470265744.322332 ARGENTINA | 1996 | 472082095.757644 ARGENTINA | 1995 | 472238150.79081 ARGENTINA | 1994 | 472714844.720526 ARGENTINA | 1993 | 465814592.97729 ARGENTINA | 1992 | 464372128.80555 BRAZIL | 1998 | 270943031.358052 BRAZIL | 1997 | 461570917.545876 BRAZIL | 1996 | 464515326.419688 BRAZIL | 1995 | 458355825.23166 BRAZIL | 1994 | 460239472.187292 BRAZIL | 1993 | 461119787.291204 BRAZIL | 1992 | 466758634.291964 CANADA | 1998 | 285268720.932803 CANADA | 1997 | 484758725.100009 CANADA | 1996 | 485700431.980344 CANADA | 1995 | 489125389.232455 CANADA | 1994 | 493039852.627479 CANADA | 1993 | 484494844.628174 CANADA | 1992 | 488960991.758353 CHINA | 1998 | 265895488.74081 CHINA | 1997 | 465256240.463312 CHINA | 1996 | 471597993.82577 CHINA | 1995 | 464217179.771637 CHINA | 1994 | 464116128.864732 CHINA | 1993 | 465540906.38025 CHINA | 1992 | 466594748.77224 EGYPT | 1998 | 278127315.155364 EGYPT | 1997 | 473505771.696785 EGYPT | 1996 | 488451200.189159 EGYPT | 1995 | 483289435.079695 EGYPT | 1994 | 480307179.693315 EGYPT | 1993 | 480252689.215256 EGYPT | 1992 | 473408225.837053 ETHIOPIA | 1998 | 265600434.432643 ETHIOPIA | 1997 | 456588618.213626 ETHIOPIA | 1996 | 461789035.431472 ETHIOPIA | 1995 | 467156453.765779 ETHIOPIA | 1994 | 457610421.586316 ETHIOPIA | 1993 | 466245733.259863 ETHIOPIA | 1992 | 467494999.499257 FRANCE | 1998 | 268549597.545034 FRANCE | 1997 | 447465610.822367 FRANCE | 1996 | 458250574.193779 FRANCE | 1995 | 456300319.156377 FRANCE | 1994 | 455160610.714386 FRANCE | 1993 | 459992762.56369 FRANCE | 1992 | 458955666.880304 GERMANY | 1998 | 278158769.443591 GERMANY | 1997 | 477721487.276352 GERMANY | 1996 | 473469978.670876 GERMANY | 1995 | 476141853.01125 GERMANY | 1994 | 471789785.978452 GERMANY | 1993 | 468844783.89091 GERMANY | 1992 | 471006787.66756 INDIA | 1998 | 280101714.183948 INDIA | 1997 | 476017127.237769 INDIA | 1996 | 483951112.063213 INDIA | 1995 | 470350667.910612 INDIA | 1994 | 471688960.793827 INDIA | 1993 | 475678218.741311 INDIA | 1992 | 477449965.546392 INDONESIA | 1998 | 268265707.679293 INDONESIA | 1997 | 460468337.093522 INDONESIA | 1996 | 469435748.19008 INDONESIA | 1995 | 460217136.764253 INDONESIA | 1994 | 465000585.271601 INDONESIA | 1993 | 466768513.928187 INDONESIA | 1992 | 457749346.895578 IRAN | 1998 | 270974692.795901 IRAN | 1997 | 461407783.466604 IRAN | 1996 | 468957993.332073 IRAN | 1995 | 470722966.959465 IRAN | 1994 | 461429367.482469 IRAN | 1993 | 455638128.845788 IRAN | 1992 | 464332162.127022 IRAQ | 1998 | 280746656.055383 IRAQ | 1997 | 474318351.482819 IRAQ | 1996 | 476580206.209812 IRAQ | 1995 | 482732326.689716 IRAQ | 1994 | 479174141.160168 IRAQ | 1993 | 478492435.355069 IRAQ | 1992 | 481611089.826561 JAPAN | 1998 | 277490103.759719 JAPAN | 1997 | 467660379.373099 JAPAN | 1996 | 465130353.468339 JAPAN | 1995 | 466582574.639064 JAPAN | 1994 | 464937825.533026 JAPAN | 1993 | 462119541.352416 JAPAN | 1992 | 467661165.818809 JORDAN | 1998 | 265361790.81635 JORDAN | 1997 | 459101697.30067 JORDAN | 1996 | 457042490.111312 JORDAN | 1995 | 457528727.00353 JORDAN | 1994 | 458489550.209455 JORDAN | 1993 | 456866341.250755 JORDAN | 1992 | 454004146.434698 KENYA | 1998 | 276881839.795626 KENYA | 1997 | 464666314.91194 KENYA | 1996 | 466887590.520764 KENYA | 1995 | 463026490.67883 KENYA | 1994 | 462554764.832838 KENYA | 1993 | 463788660.682234 KENYA | 1992 | 465382773.38808 MOROCCO | 1998 | 274531473.043388 MOROCCO | 1997 | 463905924.51366 MOROCCO | 1996 | 462894131.777209 MOROCCO | 1995 | 463778829.111752 MOROCCO | 1994 | 463098428.582041 MOROCCO | 1993 | 467961454.191256 MOROCCO | 1992 | 467169891.454582 MOZAMBIQUE | 1998 | 273562929.035868 MOZAMBIQUE | 1997 | 454479221.548368 MOZAMBIQUE | 1996 | 459248840.473194 MOZAMBIQUE | 1995 | 458703032.389019 MOZAMBIQUE | 1994 | 453903155.735195 MOZAMBIQUE | 1993 | 450429220.72022 MOZAMBIQUE | 1992 | 457352896.010403 PERU | 1998 | 273140361.538811 PERU | 1997 | 456248264.463953 PERU | 1996 | 463090299.298979 PERU | 1995 | 461605836.294014 PERU | 1994 | 456836945.1949 PERU | 1993 | 459130519.93113 PERU | 1992 | 462460812.597331 ROMANIA | 1998 | 279917805.268002 ROMANIA | 1997 | 469331212.178729 ROMANIA | 1996 | 475452861.834848 ROMANIA | 1995 | 471895730.717012 ROMANIA | 1994 | 473750447.437565 ROMANIA | 1993 | 477090429.466015 ROMANIA | 1992 | 472166350.392133 RUSSIA | 1998 | 275741624.52401 RUSSIA | 1997 | 474918542.066621 RUSSIA | 1996 | 475594801.878942 RUSSIA | 1995 | 473400618.536641 RUSSIA | 1994 | 468459976.276375 RUSSIA | 1993 | 469521828.781669 RUSSIA | 1992 | 477780492.127398 SAUDI ARABIA | 1998 | 278982339.401147 SAUDI ARABIA | 1997 | 468812543.433673 SAUDI ARABIA | 1996 | 471515291.960259 SAUDI ARABIA | 1995 | 475441964.844446 SAUDI ARABIA | 1994 | 473304292.863689 SAUDI ARABIA | 1993 | 475034681.124244 SAUDI ARABIA | 1992 | 471839719.279245 UNITED KINGDOM | 1998 | 271611693.925752 UNITED KINGDOM | 1997 | 455766169.767832 UNITED KINGDOM | 1996 | 463690946.519029 UNITED KINGDOM | 1995 | 460977049.720228 UNITED KINGDOM | 1994 | 464701451.906168 UNITED KINGDOM | 1993 | 459457260.723995 UNITED KINGDOM | 1992 | 462939237.354682 UNITED STATES | 1998 | 274842228.566353 UNITED STATES | 1997 | 468012264.466215 UNITED STATES | 1996 | 472611875.341685 UNITED STATES | 1995 | 471928073.703402 UNITED STATES | 1994 | 474352374.055276 UNITED STATES | 1993 | 466121517.77461 UNITED STATES | 1992 | 475748025.416097 VIETNAM | 1998 | 272544162.024788 VIETNAM | 1997 | 462082749.893396 VIETNAM | 1996 | 468668460.454958 VIETNAM | 1995 | 464301335.130926 VIETNAM | 1994 | 464825600.996044 VIETNAM | 1993 | 472086002.584812 VIETNAM | 1992 | 465378978.529559 (175 rows) COMMIT; COMMIT