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 '%sienna%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270502840.294843 ALGERIA | 1997 | 452323255.61279 ALGERIA | 1996 | 463986300.275595 ALGERIA | 1995 | 459520550.63006 ALGERIA | 1994 | 459537652.864328 ALGERIA | 1993 | 455960761.984702 ALGERIA | 1992 | 464422639.53043 ARGENTINA | 1998 | 279112117.715015 ARGENTINA | 1997 | 474658653.070416 ARGENTINA | 1996 | 471102406.918127 ARGENTINA | 1995 | 468942420.713239 ARGENTINA | 1994 | 470285306.361866 ARGENTINA | 1993 | 468822323.210381 ARGENTINA | 1992 | 478254108.704319 BRAZIL | 1998 | 278991541.868701 BRAZIL | 1997 | 473282908.057382 BRAZIL | 1996 | 474492172.089278 BRAZIL | 1995 | 466750259.881114 BRAZIL | 1994 | 466223654.048763 BRAZIL | 1993 | 467205988.473532 BRAZIL | 1992 | 483588757.215859 CANADA | 1998 | 278494836.356786 CANADA | 1997 | 475322683.287064 CANADA | 1996 | 479746154.517786 CANADA | 1995 | 473106544.767993 CANADA | 1994 | 481174744.441246 CANADA | 1993 | 473357233.561915 CANADA | 1992 | 478894483.203017 CHINA | 1998 | 274655764.219381 CHINA | 1997 | 468569712.022506 CHINA | 1996 | 467373377.917424 CHINA | 1995 | 474734878.386296 CHINA | 1994 | 470685255.768926 CHINA | 1993 | 471232462.056054 CHINA | 1992 | 468397835.538517 EGYPT | 1998 | 269359327.451094 EGYPT | 1997 | 464743106.346537 EGYPT | 1996 | 464312989.749102 EGYPT | 1995 | 458244040.640009 EGYPT | 1994 | 458127438.248029 EGYPT | 1993 | 462936777.20098 EGYPT | 1992 | 468232175.873225 ETHIOPIA | 1998 | 271836311.169302 ETHIOPIA | 1997 | 457072419.798878 ETHIOPIA | 1996 | 460741936.91279 ETHIOPIA | 1995 | 470871235.552597 ETHIOPIA | 1994 | 454454866.568459 ETHIOPIA | 1993 | 468089308.593522 ETHIOPIA | 1992 | 461299274.641205 FRANCE | 1998 | 273015377.411165 FRANCE | 1997 | 463947903.020438 FRANCE | 1996 | 472389721.224097 FRANCE | 1995 | 465318834.330772 FRANCE | 1994 | 461848672.281913 FRANCE | 1993 | 463426545.263311 FRANCE | 1992 | 471703017.522183 GERMANY | 1998 | 276031151.89805 GERMANY | 1997 | 473116789.27413 GERMANY | 1996 | 483683018.945546 GERMANY | 1995 | 477043215.129555 GERMANY | 1994 | 475478535.594031 GERMANY | 1993 | 473236921.748591 GERMANY | 1992 | 480690073.211043 INDIA | 1998 | 271417672.357249 INDIA | 1997 | 475236053.42325 INDIA | 1996 | 465356657.222303 INDIA | 1995 | 478244211.673476 INDIA | 1994 | 477021815.923738 INDIA | 1993 | 471650815.20943 INDIA | 1992 | 482988715.158745 INDONESIA | 1998 | 272892987.883616 INDONESIA | 1997 | 464662171.806794 INDONESIA | 1996 | 468475334.013374 INDONESIA | 1995 | 457718736.58075 INDONESIA | 1994 | 464684211.749911 INDONESIA | 1993 | 463238233.845724 INDONESIA | 1992 | 458508327.448853 IRAN | 1998 | 267835906.565105 IRAN | 1997 | 466497089.061877 IRAN | 1996 | 475972133.714167 IRAN | 1995 | 463164039.825138 IRAN | 1994 | 461378562.468103 IRAN | 1993 | 461199786.004184 IRAN | 1992 | 463918479.959127 IRAQ | 1998 | 275251920.615326 IRAQ | 1997 | 470887010.503575 IRAQ | 1996 | 472176641.514711 IRAQ | 1995 | 471816221.516228 IRAQ | 1994 | 473562427.523495 IRAQ | 1993 | 462797426.942935 IRAQ | 1992 | 470786666.670775 JAPAN | 1998 | 274696037.196736 JAPAN | 1997 | 472857254.581986 JAPAN | 1996 | 476615939.963074 JAPAN | 1995 | 462759841.975907 JAPAN | 1994 | 469380584.418306 JAPAN | 1993 | 465449606.329966 JAPAN | 1992 | 472484649.931336 JORDAN | 1998 | 269763881.065968 JORDAN | 1997 | 463951329.071016 JORDAN | 1996 | 462073050.091431 JORDAN | 1995 | 466996848.245004 JORDAN | 1994 | 462976520.278948 JORDAN | 1993 | 465947270.864355 JORDAN | 1992 | 460784354.407051 KENYA | 1998 | 275595271.266703 KENYA | 1997 | 465531321.316813 KENYA | 1996 | 476209997.936058 KENYA | 1995 | 466876987.818511 KENYA | 1994 | 471934263.950693 KENYA | 1993 | 476244178.266466 KENYA | 1992 | 470674926.659383 MOROCCO | 1998 | 273689262.329682 MOROCCO | 1997 | 474347328.094063 MOROCCO | 1996 | 471076125.375081 MOROCCO | 1995 | 466264212.34676 MOROCCO | 1994 | 469693183.007755 MOROCCO | 1993 | 472771783.567075 MOROCCO | 1992 | 466738715.476013 MOZAMBIQUE | 1998 | 270648533.354445 MOZAMBIQUE | 1997 | 461817554.413161 MOZAMBIQUE | 1996 | 459362820.297701 MOZAMBIQUE | 1995 | 460116076.533836 MOZAMBIQUE | 1994 | 461527294.7615 MOZAMBIQUE | 1993 | 451462244.515823 MOZAMBIQUE | 1992 | 455159281.967043 PERU | 1998 | 273364612.330698 PERU | 1997 | 466612633.558184 PERU | 1996 | 469343165.581453 PERU | 1995 | 468440371.729576 PERU | 1994 | 469357890.357916 PERU | 1993 | 465737330.212437 PERU | 1992 | 476166023.744307 ROMANIA | 1998 | 273379669.060733 ROMANIA | 1997 | 469815590.582433 ROMANIA | 1996 | 468048125.825096 ROMANIA | 1995 | 466945583.486423 ROMANIA | 1994 | 470976142.844294 ROMANIA | 1993 | 472997742.899999 ROMANIA | 1992 | 467228296.577956 RUSSIA | 1998 | 273325028.406785 RUSSIA | 1997 | 464448662.594721 RUSSIA | 1996 | 473115252.304421 RUSSIA | 1995 | 463100947.713555 RUSSIA | 1994 | 473807494.830584 RUSSIA | 1993 | 462675640.394366 RUSSIA | 1992 | 471595648.430607 SAUDI ARABIA | 1998 | 278428365.361645 SAUDI ARABIA | 1997 | 463253141.751501 SAUDI ARABIA | 1996 | 473538206.386197 SAUDI ARABIA | 1995 | 469959275.419816 SAUDI ARABIA | 1994 | 466204526.391073 SAUDI ARABIA | 1993 | 469756134.691117 SAUDI ARABIA | 1992 | 470938306.676486 UNITED KINGDOM | 1998 | 268432513.612827 UNITED KINGDOM | 1997 | 463921276.517528 UNITED KINGDOM | 1996 | 464590367.644586 UNITED KINGDOM | 1995 | 457566980.917028 UNITED KINGDOM | 1994 | 465049418.560471 UNITED KINGDOM | 1993 | 464489220.16074 UNITED KINGDOM | 1992 | 461717608.856435 UNITED STATES | 1998 | 275837082.110773 UNITED STATES | 1997 | 480030225.841842 UNITED STATES | 1996 | 488802309.194445 UNITED STATES | 1995 | 474020759.433483 UNITED STATES | 1994 | 474830122.690332 UNITED STATES | 1993 | 486827443.48813 UNITED STATES | 1992 | 477982313.111058 VIETNAM | 1998 | 281900700.70645 VIETNAM | 1997 | 461153321.98861 VIETNAM | 1996 | 475271822.409063 VIETNAM | 1995 | 465702220.040549 VIETNAM | 1994 | 464390956.712633 VIETNAM | 1993 | 465467513.010421 VIETNAM | 1992 | 469590838.270614 (175 rows) COMMIT; COMMIT