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 | 271278772.880013 ALGERIA | 1997 | 454857337.944048 ALGERIA | 1996 | 468634241.00036 ALGERIA | 1995 | 455568462.247018 ALGERIA | 1994 | 461262518.804211 ALGERIA | 1993 | 463285863.794137 ALGERIA | 1992 | 458138108.285854 ARGENTINA | 1998 | 274106922.390024 ARGENTINA | 1997 | 466563630.734987 ARGENTINA | 1996 | 472122133.975774 ARGENTINA | 1995 | 470522427.77484 ARGENTINA | 1994 | 473925722.532982 ARGENTINA | 1993 | 473445198.709869 ARGENTINA | 1992 | 468275307.858589 BRAZIL | 1998 | 273537138.846762 BRAZIL | 1997 | 460986726.432737 BRAZIL | 1996 | 465147668.203407 BRAZIL | 1995 | 464947853.798196 BRAZIL | 1994 | 466578562.588295 BRAZIL | 1993 | 474274587.897458 BRAZIL | 1992 | 471915696.815459 CANADA | 1998 | 273679190.359318 CANADA | 1997 | 473413757.253392 CANADA | 1996 | 484420657.186546 CANADA | 1995 | 468924610.471412 CANADA | 1994 | 479015188.17833 CANADA | 1993 | 478630719.269498 CANADA | 1992 | 477882135.335283 CHINA | 1998 | 280199361.068427 CHINA | 1997 | 467149501.131632 CHINA | 1996 | 472839192.372453 CHINA | 1995 | 472549663.323827 CHINA | 1994 | 474859849.835301 CHINA | 1993 | 465476683.897279 CHINA | 1992 | 468658962.056389 EGYPT | 1998 | 270163432.419915 EGYPT | 1997 | 465760657.960945 EGYPT | 1996 | 477723828.411005 EGYPT | 1995 | 462238408.133836 EGYPT | 1994 | 466652955.642298 EGYPT | 1993 | 474112409.979243 EGYPT | 1992 | 466145263.315871 ETHIOPIA | 1998 | 275745713.12823 ETHIOPIA | 1997 | 455388530.047408 ETHIOPIA | 1996 | 462728809.98721 ETHIOPIA | 1995 | 460519655.030011 ETHIOPIA | 1994 | 457744031.698802 ETHIOPIA | 1993 | 457966100.602785 ETHIOPIA | 1992 | 458718745.632455 FRANCE | 1998 | 268458980.907874 FRANCE | 1997 | 467160793.975284 FRANCE | 1996 | 474538272.833382 FRANCE | 1995 | 467363793.478523 FRANCE | 1994 | 467670208.384698 FRANCE | 1993 | 466867150.925846 FRANCE | 1992 | 467873324.956202 GERMANY | 1998 | 271878527.735795 GERMANY | 1997 | 469815756.75519 GERMANY | 1996 | 465536986.978325 GERMANY | 1995 | 465884743.425952 GERMANY | 1994 | 461265327.914855 GERMANY | 1993 | 472852911.181919 GERMANY | 1992 | 463338278.494895 INDIA | 1998 | 277990446.86816 INDIA | 1997 | 472047387.818849 INDIA | 1996 | 480084561.256241 INDIA | 1995 | 480936523.549827 INDIA | 1994 | 470909594.598442 INDIA | 1993 | 479191726.144538 INDIA | 1992 | 475378537.657374 INDONESIA | 1998 | 276023012.651808 INDONESIA | 1997 | 466896668.793382 INDONESIA | 1996 | 464214914.418824 INDONESIA | 1995 | 468262894.103229 INDONESIA | 1994 | 470890423.24277 INDONESIA | 1993 | 463946875.233158 INDONESIA | 1992 | 471683725.505414 IRAN | 1998 | 274879231.183622 IRAN | 1997 | 472294654.697405 IRAN | 1996 | 465499374.514674 IRAN | 1995 | 462324354.219204 IRAN | 1994 | 465427463.358235 IRAN | 1993 | 471400903.445273 IRAN | 1992 | 474919592.08141 IRAQ | 1998 | 277079887.618133 IRAQ | 1997 | 470270219.094542 IRAQ | 1996 | 472971035.135515 IRAQ | 1995 | 465637762.163264 IRAQ | 1994 | 476758507.94324 IRAQ | 1993 | 475333899.223108 IRAQ | 1992 | 468497965.428657 JAPAN | 1998 | 270810677.59379 JAPAN | 1997 | 468084407.366595 JAPAN | 1996 | 459023435.970079 JAPAN | 1995 | 460479987.983832 JAPAN | 1994 | 454404387.445876 JAPAN | 1993 | 464990081.059215 JAPAN | 1992 | 460591307.218266 JORDAN | 1998 | 275533448.230105 JORDAN | 1997 | 460603582.032109 JORDAN | 1996 | 459009651.246053 JORDAN | 1995 | 462563270.792129 JORDAN | 1994 | 455583132.344913 JORDAN | 1993 | 464160875.267634 JORDAN | 1992 | 453301456.619151 KENYA | 1998 | 275680808.615159 KENYA | 1997 | 468142449.725801 KENYA | 1996 | 468404845.679009 KENYA | 1995 | 473135113.320971 KENYA | 1994 | 470514348.521973 KENYA | 1993 | 469161763.465138 KENYA | 1992 | 473185027.781267 MOROCCO | 1998 | 266088186.909574 MOROCCO | 1997 | 455686884.58349 MOROCCO | 1996 | 457724657.663867 MOROCCO | 1995 | 460240485.020836 MOROCCO | 1994 | 469835209.966267 MOROCCO | 1993 | 467749992.97263 MOROCCO | 1992 | 458232851.158904 MOZAMBIQUE | 1998 | 267849428.037304 MOZAMBIQUE | 1997 | 449850247.861393 MOZAMBIQUE | 1996 | 457972336.655149 MOZAMBIQUE | 1995 | 457838541.973944 MOZAMBIQUE | 1994 | 458901868.736032 MOZAMBIQUE | 1993 | 447335397.111233 MOZAMBIQUE | 1992 | 456640964.702462 PERU | 1998 | 276445466.057931 PERU | 1997 | 472540001.11805 PERU | 1996 | 473939085.127562 PERU | 1995 | 474856478.386764 PERU | 1994 | 470249963.05111 PERU | 1993 | 472623551.633135 PERU | 1992 | 471929616.377 ROMANIA | 1998 | 276558927.077185 ROMANIA | 1997 | 473088441.817155 ROMANIA | 1996 | 471396927.299154 ROMANIA | 1995 | 471882527.817914 ROMANIA | 1994 | 468314186.303797 ROMANIA | 1993 | 469671747.12189 ROMANIA | 1992 | 472763857.544306 RUSSIA | 1998 | 280485026.303238 RUSSIA | 1997 | 471050967.782601 RUSSIA | 1996 | 472706783.896736 RUSSIA | 1995 | 471710736.020173 RUSSIA | 1994 | 475353520.476617 RUSSIA | 1993 | 472332682.093111 RUSSIA | 1992 | 468576997.266349 SAUDI ARABIA | 1998 | 279932928.761545 SAUDI ARABIA | 1997 | 464464677.997752 SAUDI ARABIA | 1996 | 472558810.118333 SAUDI ARABIA | 1995 | 465513085.205041 SAUDI ARABIA | 1994 | 466642441.138555 SAUDI ARABIA | 1993 | 467116675.431123 SAUDI ARABIA | 1992 | 471744060.329676 UNITED KINGDOM | 1998 | 274460661.789547 UNITED KINGDOM | 1997 | 460437063.501464 UNITED KINGDOM | 1996 | 462644500.415646 UNITED KINGDOM | 1995 | 468388477.88061 UNITED KINGDOM | 1994 | 458862157.874898 UNITED KINGDOM | 1993 | 470876641.905906 UNITED KINGDOM | 1992 | 464286505.924279 UNITED STATES | 1998 | 278604609.973681 UNITED STATES | 1997 | 465242250.936229 UNITED STATES | 1996 | 473436680.821797 UNITED STATES | 1995 | 465853879.736759 UNITED STATES | 1994 | 482467057.686041 UNITED STATES | 1993 | 475667637.347121 UNITED STATES | 1992 | 474673996.651078 VIETNAM | 1998 | 273487584.427194 VIETNAM | 1997 | 460250252.037452 VIETNAM | 1996 | 470772815.961795 VIETNAM | 1995 | 462332990.956333 VIETNAM | 1994 | 464939303.221796 VIETNAM | 1993 | 466471706.926655 VIETNAM | 1992 | 462891218.09832 (175 rows) COMMIT; COMMIT