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 '%pale%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 264613447.403904 ALGERIA | 1997 | 464841313.121377 ALGERIA | 1996 | 467082726.105802 ALGERIA | 1995 | 457047191.102736 ALGERIA | 1994 | 457426246.986493 ALGERIA | 1993 | 462256198.849398 ALGERIA | 1992 | 462996754.481336 ARGENTINA | 1998 | 277128899.481486 ARGENTINA | 1997 | 472936684.596221 ARGENTINA | 1996 | 472681674.604238 ARGENTINA | 1995 | 475948729.447786 ARGENTINA | 1994 | 470112537.152612 ARGENTINA | 1993 | 471935868.134812 ARGENTINA | 1992 | 470033551.807169 BRAZIL | 1998 | 277906507.386965 BRAZIL | 1997 | 478012365.211298 BRAZIL | 1996 | 468779931.189827 BRAZIL | 1995 | 479918046.918849 BRAZIL | 1994 | 477930577.686449 BRAZIL | 1993 | 469577482.563925 BRAZIL | 1992 | 482062929.580673 CANADA | 1998 | 282129239.975924 CANADA | 1997 | 476990314.291252 CANADA | 1996 | 484790052.25514 CANADA | 1995 | 474024904.203311 CANADA | 1994 | 476796881.755899 CANADA | 1993 | 477281185.64027 CANADA | 1992 | 478480369.397862 CHINA | 1998 | 276871933.679312 CHINA | 1997 | 469348148.602254 CHINA | 1996 | 471528622.795946 CHINA | 1995 | 472208989.854727 CHINA | 1994 | 469335627.647046 CHINA | 1993 | 461131321.516078 CHINA | 1992 | 471821160.403217 EGYPT | 1998 | 271313026.813873 EGYPT | 1997 | 465904902.64893 EGYPT | 1996 | 469009754.946399 EGYPT | 1995 | 464876120.482232 EGYPT | 1994 | 466883074.715654 EGYPT | 1993 | 464045120.700674 EGYPT | 1992 | 463066563.866901 ETHIOPIA | 1998 | 267512941.170642 ETHIOPIA | 1997 | 450887234.67479 ETHIOPIA | 1996 | 449992070.526188 ETHIOPIA | 1995 | 444960376.307208 ETHIOPIA | 1994 | 456823101.593114 ETHIOPIA | 1993 | 448271813.44289 ETHIOPIA | 1992 | 454899335.54278 FRANCE | 1998 | 271805368.327456 FRANCE | 1997 | 477052483.12849 FRANCE | 1996 | 468291737.425908 FRANCE | 1995 | 458253468.597978 FRANCE | 1994 | 467220645.236081 FRANCE | 1993 | 466658494.829807 FRANCE | 1992 | 467626457.383391 GERMANY | 1998 | 279677432.51487 GERMANY | 1997 | 476875250.488693 GERMANY | 1996 | 482001239.440455 GERMANY | 1995 | 479057896.089264 GERMANY | 1994 | 473840654.287967 GERMANY | 1993 | 483716627.774618 GERMANY | 1992 | 477225696.077457 INDIA | 1998 | 271053874.812386 INDIA | 1997 | 474576281.863793 INDIA | 1996 | 472465099.628592 INDIA | 1995 | 468139615.491322 INDIA | 1994 | 472521000.342094 INDIA | 1993 | 470626731.657547 INDIA | 1992 | 477127514.237596 INDONESIA | 1998 | 273704442.458475 INDONESIA | 1997 | 469453672.38611 INDONESIA | 1996 | 469748099.471244 INDONESIA | 1995 | 461944821.454531 INDONESIA | 1994 | 459022106.316433 INDONESIA | 1993 | 463194177.235327 INDONESIA | 1992 | 459519690.037018 IRAN | 1998 | 268631184.493354 IRAN | 1997 | 464032376.861557 IRAN | 1996 | 462571395.997155 IRAN | 1995 | 461413037.153743 IRAN | 1994 | 460527997.378965 IRAN | 1993 | 455614036.912694 IRAN | 1992 | 459075053.390037 IRAQ | 1998 | 273081029.213292 IRAQ | 1997 | 469240247.036613 IRAQ | 1996 | 472184185.711267 IRAQ | 1995 | 460967203.127335 IRAQ | 1994 | 477730275.246686 IRAQ | 1993 | 471806639.422049 IRAQ | 1992 | 477359145.182944 JAPAN | 1998 | 274213931.895094 JAPAN | 1997 | 474952781.688026 JAPAN | 1996 | 467182603.034722 JAPAN | 1995 | 472780119.2052 JAPAN | 1994 | 471231407.326422 JAPAN | 1993 | 473003051.09878 JAPAN | 1992 | 478792923.26634 JORDAN | 1998 | 276286990.588805 JORDAN | 1997 | 460020810.355459 JORDAN | 1996 | 459429019.396932 JORDAN | 1995 | 460631483.518291 JORDAN | 1994 | 460136285.299322 JORDAN | 1993 | 466407651.47914 JORDAN | 1992 | 464254263.253196 KENYA | 1998 | 273451124.810818 KENYA | 1997 | 472566452.698687 KENYA | 1996 | 468743792.543053 KENYA | 1995 | 476949244.751907 KENYA | 1994 | 472442371.185281 KENYA | 1993 | 471437392.466051 KENYA | 1992 | 472874182.919912 MOROCCO | 1998 | 276960465.053242 MOROCCO | 1997 | 472121762.161631 MOROCCO | 1996 | 472151839.976793 MOROCCO | 1995 | 463889314.931353 MOROCCO | 1994 | 474281791.974737 MOROCCO | 1993 | 469449999.810046 MOROCCO | 1992 | 474338842.305846 MOZAMBIQUE | 1998 | 268340864.104355 MOZAMBIQUE | 1997 | 453356107.524444 MOZAMBIQUE | 1996 | 457737859.845728 MOZAMBIQUE | 1995 | 459752205.472206 MOZAMBIQUE | 1994 | 455947052.270077 MOZAMBIQUE | 1993 | 460309656.554343 MOZAMBIQUE | 1992 | 450410815.397673 PERU | 1998 | 275745658.882103 PERU | 1997 | 467708874.090551 PERU | 1996 | 469639364.536831 PERU | 1995 | 463966078.926625 PERU | 1994 | 468136994.276605 PERU | 1993 | 467242438.118643 PERU | 1992 | 472747995.46754 ROMANIA | 1998 | 272895649.638305 ROMANIA | 1997 | 462535083.240663 ROMANIA | 1996 | 468153617.144829 ROMANIA | 1995 | 468928461.967515 ROMANIA | 1994 | 464853532.786279 ROMANIA | 1993 | 465481469.58918 ROMANIA | 1992 | 466692994.672107 RUSSIA | 1998 | 274482570.163686 RUSSIA | 1997 | 467143487.785783 RUSSIA | 1996 | 467210197.57875 RUSSIA | 1995 | 468530037.825345 RUSSIA | 1994 | 469564781.868605 RUSSIA | 1993 | 466125477.977895 RUSSIA | 1992 | 479283404.673035 SAUDI ARABIA | 1998 | 279806845.962663 SAUDI ARABIA | 1997 | 468786046.715154 SAUDI ARABIA | 1996 | 460238978.437013 SAUDI ARABIA | 1995 | 474627862.734323 SAUDI ARABIA | 1994 | 469417855.713876 SAUDI ARABIA | 1993 | 467225714.881974 SAUDI ARABIA | 1992 | 469459461.168102 UNITED KINGDOM | 1998 | 270908225.616473 UNITED KINGDOM | 1997 | 464422870.266629 UNITED KINGDOM | 1996 | 463526975.869435 UNITED KINGDOM | 1995 | 462012440.443616 UNITED KINGDOM | 1994 | 458463570.505859 UNITED KINGDOM | 1993 | 458527991.694468 UNITED KINGDOM | 1992 | 458178053.807013 UNITED STATES | 1998 | 279754989.470787 UNITED STATES | 1997 | 477709081.897068 UNITED STATES | 1996 | 480226463.95298 UNITED STATES | 1995 | 479746364.267991 UNITED STATES | 1994 | 485305063.68176 UNITED STATES | 1993 | 480016007.653294 UNITED STATES | 1992 | 480347671.139937 VIETNAM | 1998 | 273623381.055546 VIETNAM | 1997 | 463254522.184466 VIETNAM | 1996 | 459256937.910288 VIETNAM | 1995 | 464451888.693587 VIETNAM | 1994 | 456688871.584173 VIETNAM | 1993 | 464850475.418296 VIETNAM | 1992 | 467482112.594094 (175 rows) COMMIT; COMMIT