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 '%brown%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 268695446.274105 ALGERIA | 1997 | 458981674.194041 ALGERIA | 1996 | 458042417.830063 ALGERIA | 1995 | 455339226.753909 ALGERIA | 1994 | 459811726.87193 ALGERIA | 1993 | 459841287.57931 ALGERIA | 1992 | 461742403.66818 ARGENTINA | 1998 | 272683406.090068 ARGENTINA | 1997 | 464242993.02115 ARGENTINA | 1996 | 470943182.428913 ARGENTINA | 1995 | 468451119.513286 ARGENTINA | 1994 | 466419226.747548 ARGENTINA | 1993 | 470688123.685142 ARGENTINA | 1992 | 465357352.876863 BRAZIL | 1998 | 273093383.141596 BRAZIL | 1997 | 463465493.986144 BRAZIL | 1996 | 458165654.420741 BRAZIL | 1995 | 453893135.53493 BRAZIL | 1994 | 459152127.944394 BRAZIL | 1993 | 463656410.466029 BRAZIL | 1992 | 463516999.421607 CANADA | 1998 | 281506419.565041 CANADA | 1997 | 470264519.754608 CANADA | 1996 | 479412399.215805 CANADA | 1995 | 476053890.717737 CANADA | 1994 | 479336352.682493 CANADA | 1993 | 474480260.545243 CANADA | 1992 | 475452995.52968 CHINA | 1998 | 268113797.715572 CHINA | 1997 | 459971409.796224 CHINA | 1996 | 464727305.047852 CHINA | 1995 | 467046908.736027 CHINA | 1994 | 460514382.843548 CHINA | 1993 | 461827778.239341 CHINA | 1992 | 462585010.109904 EGYPT | 1998 | 269773873.045383 EGYPT | 1997 | 464655205.102358 EGYPT | 1996 | 469214482.326428 EGYPT | 1995 | 465941828.980183 EGYPT | 1994 | 460491074.111253 EGYPT | 1993 | 462150465.801934 EGYPT | 1992 | 460795362.526764 ETHIOPIA | 1998 | 266017284.542311 ETHIOPIA | 1997 | 450434230.508033 ETHIOPIA | 1996 | 455770220.446624 ETHIOPIA | 1995 | 447624325.300956 ETHIOPIA | 1994 | 454884889.496062 ETHIOPIA | 1993 | 446784701.440116 ETHIOPIA | 1992 | 454703197.632138 FRANCE | 1998 | 270497247.877982 FRANCE | 1997 | 457965277.008347 FRANCE | 1996 | 457401835.822975 FRANCE | 1995 | 468616775.975805 FRANCE | 1994 | 459826955.097462 FRANCE | 1993 | 463196428.209968 FRANCE | 1992 | 455229578.841614 GERMANY | 1998 | 277253420.215276 GERMANY | 1997 | 471806640.928114 GERMANY | 1996 | 466925368.03861 GERMANY | 1995 | 476062398.624787 GERMANY | 1994 | 475471955.212396 GERMANY | 1993 | 470148910.166281 GERMANY | 1992 | 472464328.163126 INDIA | 1998 | 280401974.120513 INDIA | 1997 | 478634460.906176 INDIA | 1996 | 470771867.714952 INDIA | 1995 | 476970222.907673 INDIA | 1994 | 482000550.224096 INDIA | 1993 | 473304638.857307 INDIA | 1992 | 483586666.380508 INDONESIA | 1998 | 266792476.254861 INDONESIA | 1997 | 452671213.355693 INDONESIA | 1996 | 454609352.672209 INDONESIA | 1995 | 453144248.841063 INDONESIA | 1994 | 457781206.290806 INDONESIA | 1993 | 460756276.317572 INDONESIA | 1992 | 459331537.342976 IRAN | 1998 | 273342702.28149 IRAN | 1997 | 462306326.112024 IRAN | 1996 | 468029934.499741 IRAN | 1995 | 468812924.881756 IRAN | 1994 | 459602142.197913 IRAN | 1993 | 464852129.326941 IRAN | 1992 | 465908357.957486 IRAQ | 1998 | 283434561.038018 IRAQ | 1997 | 472206522.723711 IRAQ | 1996 | 470693223.111118 IRAQ | 1995 | 474355312.123308 IRAQ | 1994 | 466644989.094268 IRAQ | 1993 | 470328067.503022 IRAQ | 1992 | 474795606.33428 JAPAN | 1998 | 268901567.73487 JAPAN | 1997 | 456388052.294831 JAPAN | 1996 | 455670664.231165 JAPAN | 1995 | 448197725.973715 JAPAN | 1994 | 447500829.812166 JAPAN | 1993 | 453547513.537645 JAPAN | 1992 | 456567077.293103 JORDAN | 1998 | 273680112.947624 JORDAN | 1997 | 459108014.046327 JORDAN | 1996 | 463633367.099716 JORDAN | 1995 | 460656847.372836 JORDAN | 1994 | 462256724.817576 JORDAN | 1993 | 457321882.035923 JORDAN | 1992 | 455487991.900308 KENYA | 1998 | 282798777.640067 KENYA | 1997 | 467329615.364919 KENYA | 1996 | 476436713.32225 KENYA | 1995 | 473020223.498636 KENYA | 1994 | 461525018.154942 KENYA | 1993 | 475823520.668438 KENYA | 1992 | 469967212.674297 MOROCCO | 1998 | 268176018.744843 MOROCCO | 1997 | 471865779.69049 MOROCCO | 1996 | 470321749.424034 MOROCCO | 1995 | 467883532.624441 MOROCCO | 1994 | 473198679.300205 MOROCCO | 1993 | 470822276.294049 MOROCCO | 1992 | 474958322.643203 MOZAMBIQUE | 1998 | 274819177.54869 MOZAMBIQUE | 1997 | 467736984.45949 MOZAMBIQUE | 1996 | 471958853.979183 MOZAMBIQUE | 1995 | 462000921.773118 MOZAMBIQUE | 1994 | 476905612.768239 MOZAMBIQUE | 1993 | 466538587.86044 MOZAMBIQUE | 1992 | 468573453.237399 PERU | 1998 | 281421534.290918 PERU | 1997 | 461754167.713848 PERU | 1996 | 465663827.168847 PERU | 1995 | 459800255.723133 PERU | 1994 | 464526307.762385 PERU | 1993 | 464384600.223362 PERU | 1992 | 465952752.240701 ROMANIA | 1998 | 272594339.145671 ROMANIA | 1997 | 466878594.588147 ROMANIA | 1996 | 466509896.446511 ROMANIA | 1995 | 464404900.665028 ROMANIA | 1994 | 470469129.094612 ROMANIA | 1993 | 468794238.064925 ROMANIA | 1992 | 469525198.319402 RUSSIA | 1998 | 280843756.995142 RUSSIA | 1997 | 471640256.818462 RUSSIA | 1996 | 478537187.197829 RUSSIA | 1995 | 467800378.910137 RUSSIA | 1994 | 474559751.506496 RUSSIA | 1993 | 467793017.328238 RUSSIA | 1992 | 475033009.690026 SAUDI ARABIA | 1998 | 274250781.355126 SAUDI ARABIA | 1997 | 462176053.462722 SAUDI ARABIA | 1996 | 463327129.541043 SAUDI ARABIA | 1995 | 466728446.386815 SAUDI ARABIA | 1994 | 459404140.163993 SAUDI ARABIA | 1993 | 458281063.09769 SAUDI ARABIA | 1992 | 465893827.148399 UNITED KINGDOM | 1998 | 262003704.41507 UNITED KINGDOM | 1997 | 458977293.964281 UNITED KINGDOM | 1996 | 457572663.297939 UNITED KINGDOM | 1995 | 457680068.077182 UNITED KINGDOM | 1994 | 453256325.188555 UNITED KINGDOM | 1993 | 464650499.86811 UNITED KINGDOM | 1992 | 452676881.091542 UNITED STATES | 1998 | 270638968.245174 UNITED STATES | 1997 | 460612555.777294 UNITED STATES | 1996 | 457654707.628393 UNITED STATES | 1995 | 458149081.35822 UNITED STATES | 1994 | 460615504.602733 UNITED STATES | 1993 | 457448336.20368 UNITED STATES | 1992 | 460482834.489453 VIETNAM | 1998 | 266990875.599544 VIETNAM | 1997 | 460756647.705049 VIETNAM | 1996 | 466357889.953123 VIETNAM | 1995 | 464349552.64115 VIETNAM | 1994 | 467512565.879279 VIETNAM | 1993 | 464069186.404282 VIETNAM | 1992 | 467754218.38594 (175 rows) COMMIT; COMMIT