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 '%rose%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 267947321.940457 ALGERIA | 1997 | 448858749.206986 ALGERIA | 1996 | 463510412.385696 ALGERIA | 1995 | 452321678.109131 ALGERIA | 1994 | 457967837.937366 ALGERIA | 1993 | 460649293.864132 ALGERIA | 1992 | 465608688.41589 ARGENTINA | 1998 | 273398150.501777 ARGENTINA | 1997 | 464266801.242472 ARGENTINA | 1996 | 461124092.645409 ARGENTINA | 1995 | 461455251.197153 ARGENTINA | 1994 | 469548057.150626 ARGENTINA | 1993 | 463916716.898264 ARGENTINA | 1992 | 456554296.163193 BRAZIL | 1998 | 272616095.619629 BRAZIL | 1997 | 458355434.84543 BRAZIL | 1996 | 463991444.032635 BRAZIL | 1995 | 456963126.599439 BRAZIL | 1994 | 463192765.576092 BRAZIL | 1993 | 464103685.928833 BRAZIL | 1992 | 461088016.979931 CANADA | 1998 | 275040514.421182 CANADA | 1997 | 470813685.026886 CANADA | 1996 | 472039643.650801 CANADA | 1995 | 476204789.093372 CANADA | 1994 | 473723866.337958 CANADA | 1993 | 475082474.634808 CANADA | 1992 | 477781007.339538 CHINA | 1998 | 271835954.106331 CHINA | 1997 | 461988869.977159 CHINA | 1996 | 455375932.604847 CHINA | 1995 | 467600154.953383 CHINA | 1994 | 465256430.424685 CHINA | 1993 | 459339011.455805 CHINA | 1992 | 468359552.858326 EGYPT | 1998 | 272322817.928584 EGYPT | 1997 | 462283847.054728 EGYPT | 1996 | 470873374.125256 EGYPT | 1995 | 458282758.379421 EGYPT | 1994 | 464736795.956614 EGYPT | 1993 | 466794837.783059 EGYPT | 1992 | 459416864.497669 ETHIOPIA | 1998 | 269045953.991074 ETHIOPIA | 1997 | 456564347.978278 ETHIOPIA | 1996 | 452953917.859754 ETHIOPIA | 1995 | 450296415.032182 ETHIOPIA | 1994 | 464563838.587572 ETHIOPIA | 1993 | 454317786.353161 ETHIOPIA | 1992 | 459092026.109744 FRANCE | 1998 | 267937238.330875 FRANCE | 1997 | 456951272.047679 FRANCE | 1996 | 461283188.629382 FRANCE | 1995 | 453437378.543826 FRANCE | 1994 | 464605554.513192 FRANCE | 1993 | 466078887.671959 FRANCE | 1992 | 457951151.618758 GERMANY | 1998 | 281883747.216855 GERMANY | 1997 | 475623427.994168 GERMANY | 1996 | 483964129.396112 GERMANY | 1995 | 476195374.140947 GERMANY | 1994 | 476767218.306881 GERMANY | 1993 | 482714394.028362 GERMANY | 1992 | 477920634.682641 INDIA | 1998 | 279439305.869866 INDIA | 1997 | 476808098.963344 INDIA | 1996 | 477889936.660667 INDIA | 1995 | 479798805.660221 INDIA | 1994 | 477820587.062058 INDIA | 1993 | 480412835.342328 INDIA | 1992 | 478367274.06958 INDONESIA | 1998 | 271966569.61526 INDONESIA | 1997 | 457156669.521591 INDONESIA | 1996 | 456327868.337 INDONESIA | 1995 | 453571680.848331 INDONESIA | 1994 | 461288538.470241 INDONESIA | 1993 | 458661734.328188 INDONESIA | 1992 | 460445981.038195 IRAN | 1998 | 270185267.0324 IRAN | 1997 | 468597378.515003 IRAN | 1996 | 462186820.777335 IRAN | 1995 | 466936793.813584 IRAN | 1994 | 464856828.503469 IRAN | 1993 | 459432038.184806 IRAN | 1992 | 469297949.787661 IRAQ | 1998 | 282347297.151834 IRAQ | 1997 | 468913731.715949 IRAQ | 1996 | 476076763.823113 IRAQ | 1995 | 478935712.389725 IRAQ | 1994 | 473719413.182478 IRAQ | 1993 | 470363411.642624 IRAQ | 1992 | 472959686.26411 JAPAN | 1998 | 265161196.176942 JAPAN | 1997 | 468905692.259008 JAPAN | 1996 | 464330593.556291 JAPAN | 1995 | 467133428.137237 JAPAN | 1994 | 466850131.726075 JAPAN | 1993 | 464702614.106692 JAPAN | 1992 | 468845607.694062 JORDAN | 1998 | 275304492.281739 JORDAN | 1997 | 463072696.880015 JORDAN | 1996 | 463614161.855876 JORDAN | 1995 | 465639856.748885 JORDAN | 1994 | 469945136.585978 JORDAN | 1993 | 458015369.467028 JORDAN | 1992 | 459902285.814454 KENYA | 1998 | 275192450.643787 KENYA | 1997 | 462742626.815542 KENYA | 1996 | 468995488.05933 KENYA | 1995 | 464128390.94753 KENYA | 1994 | 463852628.839261 KENYA | 1993 | 466020183.82758 KENYA | 1992 | 462236438.438799 MOROCCO | 1998 | 269493937.034104 MOROCCO | 1997 | 465778283.801172 MOROCCO | 1996 | 468789878.896054 MOROCCO | 1995 | 468457229.765237 MOROCCO | 1994 | 472974461.229323 MOROCCO | 1993 | 475492682.049712 MOROCCO | 1992 | 468711667.450343 MOZAMBIQUE | 1998 | 272558824.411202 MOZAMBIQUE | 1997 | 460949905.074195 MOZAMBIQUE | 1996 | 458293094.797027 MOZAMBIQUE | 1995 | 459460995.501132 MOZAMBIQUE | 1994 | 467508053.700703 MOZAMBIQUE | 1993 | 459506735.601929 MOZAMBIQUE | 1992 | 460796276.791108 PERU | 1998 | 276585497.24186 PERU | 1997 | 458994530.910115 PERU | 1996 | 462775397.187866 PERU | 1995 | 460555416.841787 PERU | 1994 | 461675444.204384 PERU | 1993 | 459911261.794584 PERU | 1992 | 467400312.254689 ROMANIA | 1998 | 275217822.646034 ROMANIA | 1997 | 463718702.727232 ROMANIA | 1996 | 473241818.981321 ROMANIA | 1995 | 469548894.231833 ROMANIA | 1994 | 467433711.930741 ROMANIA | 1993 | 470568622.451721 ROMANIA | 1992 | 472601816.821953 RUSSIA | 1998 | 274873744.663701 RUSSIA | 1997 | 470855346.158761 RUSSIA | 1996 | 475000696.969039 RUSSIA | 1995 | 466288946.909365 RUSSIA | 1994 | 463455578.905214 RUSSIA | 1993 | 457608897.64164 RUSSIA | 1992 | 470542347.424 SAUDI ARABIA | 1998 | 284494995.98002 SAUDI ARABIA | 1997 | 475144119.825812 SAUDI ARABIA | 1996 | 470124468.556146 SAUDI ARABIA | 1995 | 483009572.337034 SAUDI ARABIA | 1994 | 475450552.136654 SAUDI ARABIA | 1993 | 474926216.207277 SAUDI ARABIA | 1992 | 474468838.62123 UNITED KINGDOM | 1998 | 268617435.468796 UNITED KINGDOM | 1997 | 452079427.163233 UNITED KINGDOM | 1996 | 451114744.032703 UNITED KINGDOM | 1995 | 449956896.224915 UNITED KINGDOM | 1994 | 454518369.913107 UNITED KINGDOM | 1993 | 447935467.062058 UNITED KINGDOM | 1992 | 450914104.025854 UNITED STATES | 1998 | 270251314.722705 UNITED STATES | 1997 | 463552512.624262 UNITED STATES | 1996 | 463516329.406126 UNITED STATES | 1995 | 458815013.757722 UNITED STATES | 1994 | 463762807.924171 UNITED STATES | 1993 | 469507297.764099 UNITED STATES | 1992 | 468019297.610516 VIETNAM | 1998 | 275811129.483044 VIETNAM | 1997 | 462929674.637498 VIETNAM | 1996 | 464739750.305882 VIETNAM | 1995 | 462877670.136778 VIETNAM | 1994 | 464609136.988667 VIETNAM | 1993 | 464959612.596411 VIETNAM | 1992 | 463496004.870722 (175 rows) COMMIT; COMMIT