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 '%drab%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 263647298.019717 ALGERIA | 1997 | 456464987.788935 ALGERIA | 1996 | 449045723.500168 ALGERIA | 1995 | 455574761.044927 ALGERIA | 1994 | 460357181.734475 ALGERIA | 1993 | 460860739.558486 ALGERIA | 1992 | 458705021.913666 ARGENTINA | 1998 | 277476598.318584 ARGENTINA | 1997 | 465131184.249316 ARGENTINA | 1996 | 477248732.252453 ARGENTINA | 1995 | 464628317.967973 ARGENTINA | 1994 | 469102104.538947 ARGENTINA | 1993 | 480766794.680411 ARGENTINA | 1992 | 471608828.027633 BRAZIL | 1998 | 274215658.951948 BRAZIL | 1997 | 464238567.970118 BRAZIL | 1996 | 472284279.23207 BRAZIL | 1995 | 464529766.488053 BRAZIL | 1994 | 467798523.720471 BRAZIL | 1993 | 468018870.74106 BRAZIL | 1992 | 469818074.177098 CANADA | 1998 | 281151417.114442 CANADA | 1997 | 479132784.615832 CANADA | 1996 | 487669957.919807 CANADA | 1995 | 484696501.360391 CANADA | 1994 | 488239225.431471 CANADA | 1993 | 478788548.773178 CANADA | 1992 | 478963682.174445 CHINA | 1998 | 278391055.938449 CHINA | 1997 | 475809821.124687 CHINA | 1996 | 476765848.154263 CHINA | 1995 | 476553149.677958 CHINA | 1994 | 464042054.35794 CHINA | 1993 | 472465902.736819 CHINA | 1992 | 473800613.500633 EGYPT | 1998 | 278352580.002146 EGYPT | 1997 | 470335047.274512 EGYPT | 1996 | 472863965.928716 EGYPT | 1995 | 477033297.417256 EGYPT | 1994 | 472251983.813224 EGYPT | 1993 | 473604044.34872 EGYPT | 1992 | 471332268.45841 ETHIOPIA | 1998 | 279995538.093897 ETHIOPIA | 1997 | 471231625.56883 ETHIOPIA | 1996 | 466650573.255653 ETHIOPIA | 1995 | 475527852.971222 ETHIOPIA | 1994 | 473984303.57744 ETHIOPIA | 1993 | 474919814.092078 ETHIOPIA | 1992 | 478751184.285019 FRANCE | 1998 | 272255941.604673 FRANCE | 1997 | 466330957.661212 FRANCE | 1996 | 466584504.937239 FRANCE | 1995 | 468956654.169907 FRANCE | 1994 | 467253935.526293 FRANCE | 1993 | 465425894.230541 FRANCE | 1992 | 465546362.800869 GERMANY | 1998 | 280920924.558643 GERMANY | 1997 | 470385207.822785 GERMANY | 1996 | 472832698.061454 GERMANY | 1995 | 471202663.966406 GERMANY | 1994 | 484775992.679376 GERMANY | 1993 | 475998452.274928 GERMANY | 1992 | 477795441.174778 INDIA | 1998 | 279563647.098579 INDIA | 1997 | 473414897.223025 INDIA | 1996 | 479565681.624698 INDIA | 1995 | 474123252.463707 INDIA | 1994 | 477175944.715244 INDIA | 1993 | 473563557.392009 INDIA | 1992 | 480404729.877689 INDONESIA | 1998 | 276176928.759486 INDONESIA | 1997 | 463057899.071539 INDONESIA | 1996 | 477948797.318108 INDONESIA | 1995 | 475060064.267161 INDONESIA | 1994 | 463442024.279145 INDONESIA | 1993 | 468354970.018437 INDONESIA | 1992 | 466220026.024509 IRAN | 1998 | 274891328.754442 IRAN | 1997 | 468799589.431279 IRAN | 1996 | 471852060.04495 IRAN | 1995 | 475934068.581224 IRAN | 1994 | 471188316.427595 IRAN | 1993 | 479548651.800941 IRAN | 1992 | 478232771.446719 IRAQ | 1998 | 280382044.023187 IRAQ | 1997 | 468991466.738958 IRAQ | 1996 | 476207512.34252 IRAQ | 1995 | 478266813.592639 IRAQ | 1994 | 479597316.972525 IRAQ | 1993 | 479840560.111352 IRAQ | 1992 | 487190278.590837 JAPAN | 1998 | 271254474.336746 JAPAN | 1997 | 459787301.044787 JAPAN | 1996 | 468032469.149656 JAPAN | 1995 | 464379724.22515 JAPAN | 1994 | 467431962.130424 JAPAN | 1993 | 469547695.946602 JAPAN | 1992 | 464652613.079826 JORDAN | 1998 | 268230306.369829 JORDAN | 1997 | 461138055.528357 JORDAN | 1996 | 458770470.502082 JORDAN | 1995 | 454387750.586449 JORDAN | 1994 | 461768084.673357 JORDAN | 1993 | 456006233.32558 JORDAN | 1992 | 465001627.201084 KENYA | 1998 | 271016532.819396 KENYA | 1997 | 465806226.320448 KENYA | 1996 | 474390139.951781 KENYA | 1995 | 467915547.838479 KENYA | 1994 | 471258300.319059 KENYA | 1993 | 462007807.073454 KENYA | 1992 | 471908721.831351 MOROCCO | 1998 | 277035480.159291 MOROCCO | 1997 | 479871367.648863 MOROCCO | 1996 | 471662235.740278 MOROCCO | 1995 | 477490697.877785 MOROCCO | 1994 | 467791003.717078 MOROCCO | 1993 | 469080678.607615 MOROCCO | 1992 | 480449044.10899 MOZAMBIQUE | 1998 | 271262415.649418 MOZAMBIQUE | 1997 | 461810260.622047 MOZAMBIQUE | 1996 | 463458542.468783 MOZAMBIQUE | 1995 | 464048902.804619 MOZAMBIQUE | 1994 | 478810705.670269 MOZAMBIQUE | 1993 | 466461262.677377 MOZAMBIQUE | 1992 | 461871621.59665 PERU | 1998 | 275173249.554132 PERU | 1997 | 472483365.927905 PERU | 1996 | 464914711.15637 PERU | 1995 | 471739644.123008 PERU | 1994 | 471127663.313075 PERU | 1993 | 467627476.848803 PERU | 1992 | 469628529.436738 ROMANIA | 1998 | 270128439.540859 ROMANIA | 1997 | 463107390.709477 ROMANIA | 1996 | 469528021.118431 ROMANIA | 1995 | 462737422.539531 ROMANIA | 1994 | 467625374.36308 ROMANIA | 1993 | 459318526.530001 ROMANIA | 1992 | 467969918.595956 RUSSIA | 1998 | 280421834.571487 RUSSIA | 1997 | 475383373.546729 RUSSIA | 1996 | 474339152.303237 RUSSIA | 1995 | 468807942.831336 RUSSIA | 1994 | 468494310.454004 RUSSIA | 1993 | 463775188.656052 RUSSIA | 1992 | 466354398.965559 SAUDI ARABIA | 1998 | 281580255.290954 SAUDI ARABIA | 1997 | 470498676.031136 SAUDI ARABIA | 1996 | 469022774.474732 SAUDI ARABIA | 1995 | 472468856.850251 SAUDI ARABIA | 1994 | 465055491.452805 SAUDI ARABIA | 1993 | 469296749.859029 SAUDI ARABIA | 1992 | 469910313.251924 UNITED KINGDOM | 1998 | 276661877.473235 UNITED KINGDOM | 1997 | 462704950.577601 UNITED KINGDOM | 1996 | 464928905.929762 UNITED KINGDOM | 1995 | 463538772.739266 UNITED KINGDOM | 1994 | 469356448.128136 UNITED KINGDOM | 1993 | 466025410.957299 UNITED KINGDOM | 1992 | 467374434.737718 UNITED STATES | 1998 | 277286848.860134 UNITED STATES | 1997 | 470966502.90609 UNITED STATES | 1996 | 474343926.076864 UNITED STATES | 1995 | 465740048.336288 UNITED STATES | 1994 | 465439229.512908 UNITED STATES | 1993 | 470703864.753343 UNITED STATES | 1992 | 477154401.954575 VIETNAM | 1998 | 275056411.455887 VIETNAM | 1997 | 463666357.117954 VIETNAM | 1996 | 465355285.621967 VIETNAM | 1995 | 463154187.03316 VIETNAM | 1994 | 463701936.605393 VIETNAM | 1993 | 457197204.761955 VIETNAM | 1992 | 463424465.544026 (175 rows) COMMIT; COMMIT