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 | 268134932.593814 ALGERIA | 1997 | 448814129.609259 ALGERIA | 1996 | 463885519.000664 ALGERIA | 1995 | 451494315.069795 ALGERIA | 1994 | 457798799.396326 ALGERIA | 1993 | 460749458.360698 ALGERIA | 1992 | 467108480.103634 ARGENTINA | 1998 | 272970939.81311 ARGENTINA | 1997 | 463857501.419483 ARGENTINA | 1996 | 461207652.767537 ARGENTINA | 1995 | 461431152.664389 ARGENTINA | 1994 | 469933190.568271 ARGENTINA | 1993 | 464932112.883642 ARGENTINA | 1992 | 457351243.204681 BRAZIL | 1998 | 273132596.189233 BRAZIL | 1997 | 458189033.78928 BRAZIL | 1996 | 463788618.447183 BRAZIL | 1995 | 456412664.714341 BRAZIL | 1994 | 462892567.036409 BRAZIL | 1993 | 464883921.014074 BRAZIL | 1992 | 461663394.987778 CANADA | 1998 | 275498794.153107 CANADA | 1997 | 470542124.052866 CANADA | 1996 | 472298096.128484 CANADA | 1995 | 475211941.016707 CANADA | 1994 | 472857659.488557 CANADA | 1993 | 475222101.409287 CANADA | 1992 | 478637733.133106 CHINA | 1998 | 272310117.803549 CHINA | 1997 | 462587678.482748 CHINA | 1996 | 456313351.821547 CHINA | 1995 | 467814719.063468 CHINA | 1994 | 465415712.68985 CHINA | 1993 | 459869165.718349 CHINA | 1992 | 468596653.135159 EGYPT | 1998 | 272725305.639815 EGYPT | 1997 | 462327777.663676 EGYPT | 1996 | 470699232.503569 EGYPT | 1995 | 458435214.354838 EGYPT | 1994 | 464754237.072905 EGYPT | 1993 | 465776008.459079 EGYPT | 1992 | 458728842.49776 ETHIOPIA | 1998 | 269440336.941801 ETHIOPIA | 1997 | 457076315.628587 ETHIOPIA | 1996 | 452264387.469003 ETHIOPIA | 1995 | 451185731.967751 ETHIOPIA | 1994 | 464626701.760423 ETHIOPIA | 1993 | 454718903.198866 ETHIOPIA | 1992 | 459310895.062291 FRANCE | 1998 | 268109048.37701 FRANCE | 1997 | 457630411.26003 FRANCE | 1996 | 461446914.468798 FRANCE | 1995 | 453725951.600443 FRANCE | 1994 | 464170631.464751 FRANCE | 1993 | 465894190.889918 FRANCE | 1992 | 458134717.42342 GERMANY | 1998 | 281437613.594592 GERMANY | 1997 | 476452344.557895 GERMANY | 1996 | 484043881.654271 GERMANY | 1995 | 476420611.331293 GERMANY | 1994 | 476411984.911666 GERMANY | 1993 | 482738942.460158 GERMANY | 1992 | 477932812.591846 INDIA | 1998 | 279292447.316827 INDIA | 1997 | 477315314.090081 INDIA | 1996 | 477454141.293575 INDIA | 1995 | 480024228.731966 INDIA | 1994 | 478562708.878715 INDIA | 1993 | 480439421.99956 INDIA | 1992 | 478821361.670472 INDONESIA | 1998 | 272396691.619491 INDONESIA | 1997 | 457297374.754592 INDONESIA | 1996 | 456024431.228798 INDONESIA | 1995 | 453585267.755052 INDONESIA | 1994 | 460908836.343839 INDONESIA | 1993 | 457777927.856011 INDONESIA | 1992 | 460405214.015819 IRAN | 1998 | 269623712.385581 IRAN | 1997 | 468487655.239039 IRAN | 1996 | 462293290.365909 IRAN | 1995 | 466387806.744573 IRAN | 1994 | 464975051.910044 IRAN | 1993 | 459094395.857908 IRAN | 1992 | 469029267.804466 IRAQ | 1998 | 281794145.003846 IRAQ | 1997 | 468831808.139563 IRAQ | 1996 | 476773501.838505 IRAQ | 1995 | 478384053.907051 IRAQ | 1994 | 473642611.594268 IRAQ | 1993 | 470456552.45869 IRAQ | 1992 | 473677066.905297 JAPAN | 1998 | 265590321.346769 JAPAN | 1997 | 468957341.01076 JAPAN | 1996 | 464822438.073395 JAPAN | 1995 | 467215509.721057 JAPAN | 1994 | 466643493.977841 JAPAN | 1993 | 464225543.594273 JAPAN | 1992 | 469231418.212588 JORDAN | 1998 | 275692151.254255 JORDAN | 1997 | 463534067.569738 JORDAN | 1996 | 464356083.078667 JORDAN | 1995 | 465714307.643322 JORDAN | 1994 | 469902426.385153 JORDAN | 1993 | 458459379.512357 JORDAN | 1992 | 459032412.963978 KENYA | 1998 | 274761114.160242 KENYA | 1997 | 463926136.761975 KENYA | 1996 | 468604152.169174 KENYA | 1995 | 463543964.70274 KENYA | 1994 | 463178501.307593 KENYA | 1993 | 466568320.760561 KENYA | 1992 | 462499646.929964 MOROCCO | 1998 | 269426111.992391 MOROCCO | 1997 | 465762663.999712 MOROCCO | 1996 | 468523568.211054 MOROCCO | 1995 | 468884857.652745 MOROCCO | 1994 | 473047836.931435 MOROCCO | 1993 | 475905606.154995 MOROCCO | 1992 | 468197055.807723 MOZAMBIQUE | 1998 | 271963290.372083 MOZAMBIQUE | 1997 | 461688688.77462 MOZAMBIQUE | 1996 | 458435083.999734 MOZAMBIQUE | 1995 | 459618993.317606 MOZAMBIQUE | 1994 | 466913368.165193 MOZAMBIQUE | 1993 | 459684611.403027 MOZAMBIQUE | 1992 | 461186887.503055 PERU | 1998 | 276393290.317448 PERU | 1997 | 458900546.510183 PERU | 1996 | 463015408.455128 PERU | 1995 | 460249934.808195 PERU | 1994 | 461700515.232028 PERU | 1993 | 460779691.650903 PERU | 1992 | 467191927.052388 ROMANIA | 1998 | 275290101.311052 ROMANIA | 1997 | 463619971.021845 ROMANIA | 1996 | 473571207.350961 ROMANIA | 1995 | 469997201.949414 ROMANIA | 1994 | 467155751.711812 ROMANIA | 1993 | 471109945.060231 ROMANIA | 1992 | 473136648.160554 RUSSIA | 1998 | 274879015.45602 RUSSIA | 1997 | 470421124.681586 RUSSIA | 1996 | 474904170.812562 RUSSIA | 1995 | 466099367.614331 RUSSIA | 1994 | 462616129.199876 RUSSIA | 1993 | 457907421.548746 RUSSIA | 1992 | 470467410.354064 SAUDI ARABIA | 1998 | 284558414.850914 SAUDI ARABIA | 1997 | 475890891.731602 SAUDI ARABIA | 1996 | 470200409.72475 SAUDI ARABIA | 1995 | 483488403.101069 SAUDI ARABIA | 1994 | 475966393.958413 SAUDI ARABIA | 1993 | 474581178.963473 SAUDI ARABIA | 1992 | 474560010.055052 UNITED KINGDOM | 1998 | 269395619.119059 UNITED KINGDOM | 1997 | 452427343.796493 UNITED KINGDOM | 1996 | 450379713.718657 UNITED KINGDOM | 1995 | 450324448.269249 UNITED KINGDOM | 1994 | 454621111.50534 UNITED KINGDOM | 1993 | 448220467.471922 UNITED KINGDOM | 1992 | 450831615.957478 UNITED STATES | 1998 | 270410584.08741 UNITED STATES | 1997 | 463732263.77631 UNITED STATES | 1996 | 462693101.244475 UNITED STATES | 1995 | 458651916.544568 UNITED STATES | 1994 | 463917608.450124 UNITED STATES | 1993 | 469577782.573057 UNITED STATES | 1992 | 468639974.250334 VIETNAM | 1998 | 276139750.584877 VIETNAM | 1997 | 463911077.815098 VIETNAM | 1996 | 464893264.408109 VIETNAM | 1995 | 462752622.808579 VIETNAM | 1994 | 463946277.935155 VIETNAM | 1993 | 464729662.796109 VIETNAM | 1992 | 463803521.289541 (175 rows) COMMIT; COMMIT