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 '%rosy%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270569735.712362 ALGERIA | 1997 | 454081534.572633 ALGERIA | 1996 | 468015823.736148 ALGERIA | 1995 | 455740551.784715 ALGERIA | 1994 | 460622461.806082 ALGERIA | 1993 | 462281638.659231 ALGERIA | 1992 | 457639850.407793 ARGENTINA | 1998 | 273849899.00905 ARGENTINA | 1997 | 465830448.208727 ARGENTINA | 1996 | 471461432.784645 ARGENTINA | 1995 | 469931745.991004 ARGENTINA | 1994 | 473604892.006095 ARGENTINA | 1993 | 472073740.214591 ARGENTINA | 1992 | 468095246.163345 BRAZIL | 1998 | 273426350.542759 BRAZIL | 1997 | 460072426.211106 BRAZIL | 1996 | 464302042.08082 BRAZIL | 1995 | 464870467.579055 BRAZIL | 1994 | 466048362.63916 BRAZIL | 1993 | 474407694.68215 BRAZIL | 1992 | 471803500.198004 CANADA | 1998 | 273355232.867521 CANADA | 1997 | 472951755.578987 CANADA | 1996 | 484020905.138097 CANADA | 1995 | 468506707.123888 CANADA | 1994 | 478040135.627442 CANADA | 1993 | 477859245.017597 CANADA | 1992 | 477387483.876613 CHINA | 1998 | 280140944.231651 CHINA | 1997 | 467002527.936738 CHINA | 1996 | 472250005.42642 CHINA | 1995 | 472336944.698721 CHINA | 1994 | 474519317.41874 CHINA | 1993 | 464159303.800094 CHINA | 1992 | 467540425.431091 EGYPT | 1998 | 270208104.856633 EGYPT | 1997 | 464926648.571934 EGYPT | 1996 | 476993467.658843 EGYPT | 1995 | 461668197.802367 EGYPT | 1994 | 466059236.780959 EGYPT | 1993 | 474012778.164004 EGYPT | 1992 | 465339634.400562 ETHIOPIA | 1998 | 275597971.813266 ETHIOPIA | 1997 | 455021106.598512 ETHIOPIA | 1996 | 462086380.503806 ETHIOPIA | 1995 | 459757283.022616 ETHIOPIA | 1994 | 457681870.500148 ETHIOPIA | 1993 | 457563195.181195 ETHIOPIA | 1992 | 458713345.218945 FRANCE | 1998 | 268063265.545259 FRANCE | 1997 | 466211067.33746 FRANCE | 1996 | 473374379.519257 FRANCE | 1995 | 466860170.158293 FRANCE | 1994 | 467530996.151706 FRANCE | 1993 | 466719789.812441 FRANCE | 1992 | 467390222.096812 GERMANY | 1998 | 271483693.988948 GERMANY | 1997 | 469113224.788845 GERMANY | 1996 | 464911598.473001 GERMANY | 1995 | 465415350.02636 GERMANY | 1994 | 461140019.971694 GERMANY | 1993 | 471876389.48632 GERMANY | 1992 | 462862803.649567 INDIA | 1998 | 277940583.029879 INDIA | 1997 | 471942901.255103 INDIA | 1996 | 479888202.586048 INDIA | 1995 | 479954369.66785 INDIA | 1994 | 470426520.784154 INDIA | 1993 | 478644821.378669 INDIA | 1992 | 474904363.565079 INDONESIA | 1998 | 275892044.88289 INDONESIA | 1997 | 467184253.632775 INDONESIA | 1996 | 463641915.806839 INDONESIA | 1995 | 467652291.323318 INDONESIA | 1994 | 470980168.193123 INDONESIA | 1993 | 463669115.997353 INDONESIA | 1992 | 470929029.350646 IRAN | 1998 | 274142989.631957 IRAN | 1997 | 471951067.676654 IRAN | 1996 | 465465013.296852 IRAN | 1995 | 461943612.626704 IRAN | 1994 | 464897330.967871 IRAN | 1993 | 470170969.19527 IRAN | 1992 | 474369962.43154 IRAQ | 1998 | 277092655.768869 IRAQ | 1997 | 469571404.68724 IRAQ | 1996 | 472264477.836471 IRAQ | 1995 | 465514782.273867 IRAQ | 1994 | 476398800.617753 IRAQ | 1993 | 475292988.513174 IRAQ | 1992 | 468532947.500312 JAPAN | 1998 | 270497921.558646 JAPAN | 1997 | 467488104.294594 JAPAN | 1996 | 458352384.064324 JAPAN | 1995 | 459848407.19635 JAPAN | 1994 | 454393030.164633 JAPAN | 1993 | 464778194.486751 JAPAN | 1992 | 459717615.532357 JORDAN | 1998 | 275469826.001628 JORDAN | 1997 | 460256203.382111 JORDAN | 1996 | 458685628.073426 JORDAN | 1995 | 461845932.627931 JORDAN | 1994 | 454771035.886576 JORDAN | 1993 | 463764424.048211 JORDAN | 1992 | 453811663.858811 KENYA | 1998 | 275308815.321728 KENYA | 1997 | 467081072.905239 KENYA | 1996 | 468307978.472465 KENYA | 1995 | 472313998.233832 KENYA | 1994 | 469868582.622597 KENYA | 1993 | 468551096.576151 KENYA | 1992 | 472453371.522695 MOROCCO | 1998 | 265781548.489169 MOROCCO | 1997 | 455495392.512251 MOROCCO | 1996 | 457109709.886849 MOROCCO | 1995 | 459501106.590787 MOROCCO | 1994 | 469217452.168352 MOROCCO | 1993 | 466743247.154913 MOROCCO | 1992 | 458263569.560561 MOZAMBIQUE | 1998 | 268076012.176519 MOZAMBIQUE | 1997 | 449089318.081396 MOZAMBIQUE | 1996 | 457756560.737288 MOZAMBIQUE | 1995 | 457609217.351012 MOZAMBIQUE | 1994 | 458281622.725523 MOZAMBIQUE | 1993 | 446711283.720848 MOZAMBIQUE | 1992 | 456280903.259397 PERU | 1998 | 276267166.063837 PERU | 1997 | 471667546.059671 PERU | 1996 | 473635538.502769 PERU | 1995 | 473898330.429473 PERU | 1994 | 469869035.447606 PERU | 1993 | 472583769.837881 PERU | 1992 | 471242687.866309 ROMANIA | 1998 | 276290627.066334 ROMANIA | 1997 | 472703481.175324 ROMANIA | 1996 | 471091318.093937 ROMANIA | 1995 | 471773120.959803 ROMANIA | 1994 | 467311975.075917 ROMANIA | 1993 | 469497868.764336 ROMANIA | 1992 | 472411771.02492 RUSSIA | 1998 | 280134624.739121 RUSSIA | 1997 | 470519188.35656 RUSSIA | 1996 | 472475895.779187 RUSSIA | 1995 | 471988930.636886 RUSSIA | 1994 | 474545617.352487 RUSSIA | 1993 | 471766051.827986 RUSSIA | 1992 | 467849423.183962 SAUDI ARABIA | 1998 | 279557263.508056 SAUDI ARABIA | 1997 | 464047755.499342 SAUDI ARABIA | 1996 | 472135292.219639 SAUDI ARABIA | 1995 | 465123290.565148 SAUDI ARABIA | 1994 | 465763315.467412 SAUDI ARABIA | 1993 | 466485071.150186 SAUDI ARABIA | 1992 | 471236019.187591 UNITED KINGDOM | 1998 | 273750397.922705 UNITED KINGDOM | 1997 | 460359461.72472 UNITED KINGDOM | 1996 | 462282682.088129 UNITED KINGDOM | 1995 | 468182143.952933 UNITED KINGDOM | 1994 | 458108792.550371 UNITED KINGDOM | 1993 | 470062009.671801 UNITED KINGDOM | 1992 | 463876962.856738 UNITED STATES | 1998 | 278104808.529801 UNITED STATES | 1997 | 464187182.585498 UNITED STATES | 1996 | 473094467.197367 UNITED STATES | 1995 | 465613358.03288 UNITED STATES | 1994 | 481763161.209753 UNITED STATES | 1993 | 475321175.587249 UNITED STATES | 1992 | 474075693.727487 VIETNAM | 1998 | 273039984.391298 VIETNAM | 1997 | 459359987.570117 VIETNAM | 1996 | 470283213.295651 VIETNAM | 1995 | 462383163.724631 VIETNAM | 1994 | 464585720.560704 VIETNAM | 1993 | 466132002.187854 VIETNAM | 1992 | 462460908.20839 (175 rows) COMMIT; COMMIT