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 | 267476552.227026 ALGERIA | 1997 | 448475450.305845 ALGERIA | 1996 | 463509830.55148 ALGERIA | 1995 | 451766585.591677 ALGERIA | 1994 | 457517388.973217 ALGERIA | 1993 | 460146153.417094 ALGERIA | 1992 | 465946407.573501 ARGENTINA | 1998 | 273184434.263816 ARGENTINA | 1997 | 463546000.030701 ARGENTINA | 1996 | 460657845.526094 ARGENTINA | 1995 | 460991421.926961 ARGENTINA | 1994 | 469173095.654695 ARGENTINA | 1993 | 463706472.970125 ARGENTINA | 1992 | 456143586.93173 BRAZIL | 1998 | 272446663.875802 BRAZIL | 1997 | 457689407.869542 BRAZIL | 1996 | 463699001.888487 BRAZIL | 1995 | 456194003.916195 BRAZIL | 1994 | 462806583.84917 BRAZIL | 1993 | 463938352.434178 BRAZIL | 1992 | 460685577.151112 CANADA | 1998 | 274989784.241352 CANADA | 1997 | 470676284.9144 CANADA | 1996 | 471632376.102993 CANADA | 1995 | 475385497.115743 CANADA | 1994 | 472620106.970709 CANADA | 1993 | 474710643.131883 CANADA | 1992 | 477523242.479511 CHINA | 1998 | 271840054.463422 CHINA | 1997 | 462186676.033052 CHINA | 1996 | 454862095.068585 CHINA | 1995 | 467396898.682668 CHINA | 1994 | 465086090.301932 CHINA | 1993 | 459325182.060273 CHINA | 1992 | 467750851.686391 EGYPT | 1998 | 272036869.525296 EGYPT | 1997 | 461839415.113647 EGYPT | 1996 | 470786491.48126 EGYPT | 1995 | 458275471.192476 EGYPT | 1994 | 464334413.994695 EGYPT | 1993 | 465825794.057885 EGYPT | 1992 | 458780619.096509 ETHIOPIA | 1998 | 268879301.656023 ETHIOPIA | 1997 | 456114812.179549 ETHIOPIA | 1996 | 452134980.694978 ETHIOPIA | 1995 | 449370532.226049 ETHIOPIA | 1994 | 464282246.34349 ETHIOPIA | 1993 | 453901402.598539 ETHIOPIA | 1992 | 458669631.579952 FRANCE | 1998 | 267398477.701272 FRANCE | 1997 | 456246327.509004 FRANCE | 1996 | 460871654.938297 FRANCE | 1995 | 453411920.814229 FRANCE | 1994 | 464418750.793969 FRANCE | 1993 | 465580113.06748 FRANCE | 1992 | 457397370.797374 GERMANY | 1998 | 281612192.575466 GERMANY | 1997 | 475603061.987232 GERMANY | 1996 | 483475059.608181 GERMANY | 1995 | 475862675.720237 GERMANY | 1994 | 476406451.193273 GERMANY | 1993 | 482243084.317846 GERMANY | 1992 | 476985661.2293 INDIA | 1998 | 278957761.473018 INDIA | 1997 | 476788541.768291 INDIA | 1996 | 477299581.942258 INDIA | 1995 | 479299232.439605 INDIA | 1994 | 477419950.530799 INDIA | 1993 | 479821190.522373 INDIA | 1992 | 477765493.388057 INDONESIA | 1998 | 271726846.672293 INDONESIA | 1997 | 457397904.039245 INDONESIA | 1996 | 455716712.90358 INDONESIA | 1995 | 452898102.673146 INDONESIA | 1994 | 460614798.739775 INDONESIA | 1993 | 457790936.339652 INDONESIA | 1992 | 459957549.505751 IRAN | 1998 | 269486167.092687 IRAN | 1997 | 468040736.769016 IRAN | 1996 | 461739916.138118 IRAN | 1995 | 466806228.879882 IRAN | 1994 | 464927665.733673 IRAN | 1993 | 458848539.419684 IRAN | 1992 | 468776981.944214 IRAQ | 1998 | 282102303.623136 IRAQ | 1997 | 468335972.446975 IRAQ | 1996 | 475601625.078255 IRAQ | 1995 | 477949863.543598 IRAQ | 1994 | 472773713.653289 IRAQ | 1993 | 469751370.452528 IRAQ | 1992 | 472465920.258012 JAPAN | 1998 | 265081876.08606 JAPAN | 1997 | 468534982.54965 JAPAN | 1996 | 463741405.726553 JAPAN | 1995 | 466662442.165302 JAPAN | 1994 | 466331983.843307 JAPAN | 1993 | 463905343.823462 JAPAN | 1992 | 468361215.636216 JORDAN | 1998 | 275638978.559741 JORDAN | 1997 | 462854218.439971 JORDAN | 1996 | 463888703.763779 JORDAN | 1995 | 465078282.161467 JORDAN | 1994 | 469539807.791393 JORDAN | 1993 | 457779406.810385 JORDAN | 1992 | 458907807.228716 KENYA | 1998 | 274876317.35654 KENYA | 1997 | 462764571.597898 KENYA | 1996 | 468428975.296205 KENYA | 1995 | 463295545.591548 KENYA | 1994 | 463474349.673529 KENYA | 1993 | 465647534.731195 KENYA | 1992 | 461770902.854913 MOROCCO | 1998 | 269292130.580034 MOROCCO | 1997 | 465435126.49171 MOROCCO | 1996 | 468504199.287064 MOROCCO | 1995 | 468126124.865313 MOROCCO | 1994 | 472650203.442282 MOROCCO | 1993 | 475348177.586974 MOROCCO | 1992 | 468283418.443135 MOZAMBIQUE | 1998 | 272156167.438061 MOZAMBIQUE | 1997 | 460768520.379845 MOZAMBIQUE | 1996 | 458183915.565041 MOZAMBIQUE | 1995 | 458735090.227598 MOZAMBIQUE | 1994 | 467128767.355986 MOZAMBIQUE | 1993 | 459604488.482065 MOZAMBIQUE | 1992 | 460671066.620453 PERU | 1998 | 276243005.316882 PERU | 1997 | 458567471.422372 PERU | 1996 | 462607426.958625 PERU | 1995 | 459955717.358815 PERU | 1994 | 461230974.778245 PERU | 1993 | 459680265.80081 PERU | 1992 | 466725752.095132 ROMANIA | 1998 | 275077343.082777 ROMANIA | 1997 | 463202166.958688 ROMANIA | 1996 | 472981010.6488 ROMANIA | 1995 | 469035898.92928 ROMANIA | 1994 | 466782319.243916 ROMANIA | 1993 | 470099698.037413 ROMANIA | 1992 | 472079582.984305 RUSSIA | 1998 | 274792420.301537 RUSSIA | 1997 | 470088285.94961 RUSSIA | 1996 | 474499355.040196 RUSSIA | 1995 | 465321794.330357 RUSSIA | 1994 | 462827705.077536 RUSSIA | 1993 | 457283872.105704 RUSSIA | 1992 | 469917356.978098 SAUDI ARABIA | 1998 | 284234539.046896 SAUDI ARABIA | 1997 | 474569570.814167 SAUDI ARABIA | 1996 | 469597321.70839 SAUDI ARABIA | 1995 | 482720469.408254 SAUDI ARABIA | 1994 | 475476175.603591 SAUDI ARABIA | 1993 | 474613353.143311 SAUDI ARABIA | 1992 | 474052181.592372 UNITED KINGDOM | 1998 | 268621035.822258 UNITED KINGDOM | 1997 | 451705096.434138 UNITED KINGDOM | 1996 | 450126472.283969 UNITED KINGDOM | 1995 | 449610707.89715 UNITED KINGDOM | 1994 | 454196510.107566 UNITED KINGDOM | 1993 | 447391881.099193 UNITED KINGDOM | 1992 | 450255464.478385 UNITED STATES | 1998 | 269803850.52504 UNITED STATES | 1997 | 463280612.782576 UNITED STATES | 1996 | 462910916.642105 UNITED STATES | 1995 | 458159217.490961 UNITED STATES | 1994 | 463313665.607501 UNITED STATES | 1993 | 469163463.051715 UNITED STATES | 1992 | 467515663.700773 VIETNAM | 1998 | 275831350.802727 VIETNAM | 1997 | 463285101.140925 VIETNAM | 1996 | 463933415.824803 VIETNAM | 1995 | 462179230.073704 VIETNAM | 1994 | 463938135.998083 VIETNAM | 1993 | 464578377.227424 VIETNAM | 1992 | 463045749.229902 (175 rows) COMMIT; COMMIT