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 '%cream%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264286518.311246 ALGERIA | 1997 | 451765113.561437 ALGERIA | 1996 | 454595972.66511 ALGERIA | 1995 | 455133092.403516 ALGERIA | 1994 | 455184114.645513 ALGERIA | 1993 | 455718432.371417 ALGERIA | 1992 | 455911309.691711 ARGENTINA | 1998 | 268341270.81554 ARGENTINA | 1997 | 467330997.243446 ARGENTINA | 1996 | 468683230.483 ARGENTINA | 1995 | 467238473.503938 ARGENTINA | 1994 | 462361593.380405 ARGENTINA | 1993 | 464392750.266182 ARGENTINA | 1992 | 466536546.792016 BRAZIL | 1998 | 269366683.827532 BRAZIL | 1997 | 475501730.226526 BRAZIL | 1996 | 470154354.398334 BRAZIL | 1995 | 466944924.998416 BRAZIL | 1994 | 470270860.257101 BRAZIL | 1993 | 471807582.53287 BRAZIL | 1992 | 469797358.166517 CANADA | 1998 | 279294240.998693 CANADA | 1997 | 477389259.120119 CANADA | 1996 | 488674664.743026 CANADA | 1995 | 482151587.79562 CANADA | 1994 | 484978926.214546 CANADA | 1993 | 472275402.614981 CANADA | 1992 | 483980049.620775 CHINA | 1998 | 274413604.48457 CHINA | 1997 | 464007638.780828 CHINA | 1996 | 467184782.106093 CHINA | 1995 | 462497283.031842 CHINA | 1994 | 466697194.466352 CHINA | 1993 | 467947834.433392 CHINA | 1992 | 461413470.992867 EGYPT | 1998 | 270600054.822979 EGYPT | 1997 | 459928431.42238 EGYPT | 1996 | 454421851.536161 EGYPT | 1995 | 467732497.825313 EGYPT | 1994 | 459414987.540402 EGYPT | 1993 | 459378405.548267 EGYPT | 1992 | 456309929.905176 ETHIOPIA | 1998 | 273335681.760606 ETHIOPIA | 1997 | 448496903.121554 ETHIOPIA | 1996 | 455974108.286956 ETHIOPIA | 1995 | 467864151.95663 ETHIOPIA | 1994 | 454790252.655916 ETHIOPIA | 1993 | 459884786.525132 ETHIOPIA | 1992 | 467953967.301688 FRANCE | 1998 | 271459536.67886 FRANCE | 1997 | 467769559.427926 FRANCE | 1996 | 473059463.902279 FRANCE | 1995 | 461307030.568399 FRANCE | 1994 | 469480332.963103 FRANCE | 1993 | 464204904.964028 FRANCE | 1992 | 464354457.195996 GERMANY | 1998 | 275195225.299316 GERMANY | 1997 | 465632186.882984 GERMANY | 1996 | 473325103.434617 GERMANY | 1995 | 470914010.647799 GERMANY | 1994 | 470546313.484679 GERMANY | 1993 | 468145737.843618 GERMANY | 1992 | 464471921.124841 INDIA | 1998 | 272474603.137376 INDIA | 1997 | 477419458.997865 INDIA | 1996 | 474466916.017075 INDIA | 1995 | 471369148.026072 INDIA | 1994 | 472928160.283351 INDIA | 1993 | 471192962.804316 INDIA | 1992 | 474748467.779088 INDONESIA | 1998 | 272679726.423028 INDONESIA | 1997 | 462398672.873716 INDONESIA | 1996 | 456809125.738082 INDONESIA | 1995 | 453608813.622914 INDONESIA | 1994 | 452965564.342076 INDONESIA | 1993 | 458132613.448326 INDONESIA | 1992 | 455897979.646896 IRAN | 1998 | 267509793.109814 IRAN | 1997 | 468533494.812045 IRAN | 1996 | 462591914.649279 IRAN | 1995 | 464458366.534735 IRAN | 1994 | 461511372.670779 IRAN | 1993 | 472866025.84952 IRAN | 1992 | 466430496.285264 IRAQ | 1998 | 277114831.323424 IRAQ | 1997 | 468436844.263109 IRAQ | 1996 | 481936823.723752 IRAQ | 1995 | 475321222.324596 IRAQ | 1994 | 478534525.130507 IRAQ | 1993 | 469450076.024623 IRAQ | 1992 | 476255495.58524 JAPAN | 1998 | 268765348.227893 JAPAN | 1997 | 457585766.607174 JAPAN | 1996 | 459426572.312793 JAPAN | 1995 | 455877285.464882 JAPAN | 1994 | 459447494.120444 JAPAN | 1993 | 447866274.881458 JAPAN | 1992 | 449088418.818047 JORDAN | 1998 | 269830172.064953 JORDAN | 1997 | 458113975.238518 JORDAN | 1996 | 457963951.512099 JORDAN | 1995 | 455329807.760693 JORDAN | 1994 | 458577231.515719 JORDAN | 1993 | 464790736.91278 JORDAN | 1992 | 460954763.780428 KENYA | 1998 | 270025781.3187 KENYA | 1997 | 479833563.458169 KENYA | 1996 | 479122214.268896 KENYA | 1995 | 474279550.108022 KENYA | 1994 | 477513428.657532 KENYA | 1993 | 471686576.050809 KENYA | 1992 | 474808937.385823 MOROCCO | 1998 | 277382979.78006 MOROCCO | 1997 | 460745825.215579 MOROCCO | 1996 | 472212083.394703 MOROCCO | 1995 | 467903086.500867 MOROCCO | 1994 | 470122504.075625 MOROCCO | 1993 | 472207878.819275 MOROCCO | 1992 | 470795355.168111 MOZAMBIQUE | 1998 | 271128638.901914 MOZAMBIQUE | 1997 | 461633930.976795 MOZAMBIQUE | 1996 | 461942337.270123 MOZAMBIQUE | 1995 | 454075667.909887 MOZAMBIQUE | 1994 | 456726437.067814 MOZAMBIQUE | 1993 | 457869937.837324 MOZAMBIQUE | 1992 | 453988769.211009 PERU | 1998 | 272326736.883654 PERU | 1997 | 467697592.311124 PERU | 1996 | 470625645.232186 PERU | 1995 | 462726521.147973 PERU | 1994 | 459466517.867981 PERU | 1993 | 464265812.617594 PERU | 1992 | 466364875.737447 ROMANIA | 1998 | 283685064.207517 ROMANIA | 1997 | 471011465.582976 ROMANIA | 1996 | 474996263.88901 ROMANIA | 1995 | 461522281.708063 ROMANIA | 1994 | 468347974.680402 ROMANIA | 1993 | 470396989.639361 ROMANIA | 1992 | 466770460.94862 RUSSIA | 1998 | 276878798.171957 RUSSIA | 1997 | 473139021.586611 RUSSIA | 1996 | 469560801.457306 RUSSIA | 1995 | 469056799.837773 RUSSIA | 1994 | 464477751.474663 RUSSIA | 1993 | 470430823.298832 RUSSIA | 1992 | 469579420.630068 SAUDI ARABIA | 1998 | 263839540.61781 SAUDI ARABIA | 1997 | 460098151.023536 SAUDI ARABIA | 1996 | 453063847.67047 SAUDI ARABIA | 1995 | 459460217.514709 SAUDI ARABIA | 1994 | 457576100.299436 SAUDI ARABIA | 1993 | 460542058.03111 SAUDI ARABIA | 1992 | 463131152.695847 UNITED KINGDOM | 1998 | 273672228.487433 UNITED KINGDOM | 1997 | 467985292.134878 UNITED KINGDOM | 1996 | 469005206.729474 UNITED KINGDOM | 1995 | 466404124.880505 UNITED KINGDOM | 1994 | 470484194.999489 UNITED KINGDOM | 1993 | 470139750.830968 UNITED KINGDOM | 1992 | 461306998.214779 UNITED STATES | 1998 | 275340280.228344 UNITED STATES | 1997 | 475019861.846847 UNITED STATES | 1996 | 473840038.127939 UNITED STATES | 1995 | 474412975.714107 UNITED STATES | 1994 | 481050482.46825 UNITED STATES | 1993 | 474430156.758271 UNITED STATES | 1992 | 474137461.749458 VIETNAM | 1998 | 273061757.569148 VIETNAM | 1997 | 464869344.371163 VIETNAM | 1996 | 470668686.507756 VIETNAM | 1995 | 458383051.444409 VIETNAM | 1994 | 460715614.209299 VIETNAM | 1993 | 470808165.930917 VIETNAM | 1992 | 462256256.921332 (175 rows) COMMIT; COMMIT