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 '%puff%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261374561.933808 ALGERIA | 1997 | 453239464.800762 ALGERIA | 1996 | 453922029.614619 ALGERIA | 1995 | 446438387.500573 ALGERIA | 1994 | 451220811.452902 ALGERIA | 1993 | 451840748.047215 ALGERIA | 1992 | 455180402.705051 ARGENTINA | 1998 | 273864622.116908 ARGENTINA | 1997 | 469584629.926363 ARGENTINA | 1996 | 478154000.308347 ARGENTINA | 1995 | 471096933.723111 ARGENTINA | 1994 | 465109808.514607 ARGENTINA | 1993 | 471186518.017657 ARGENTINA | 1992 | 466501085.272609 BRAZIL | 1998 | 272814245.305871 BRAZIL | 1997 | 470162632.503913 BRAZIL | 1996 | 462192829.195081 BRAZIL | 1995 | 463778056.289604 BRAZIL | 1994 | 466572021.127219 BRAZIL | 1993 | 465661476.189751 BRAZIL | 1992 | 478523298.498219 CANADA | 1998 | 277887685.848926 CANADA | 1997 | 476115128.28387 CANADA | 1996 | 476541875.192751 CANADA | 1995 | 478374531.656028 CANADA | 1994 | 479565897.320906 CANADA | 1993 | 469487038.789261 CANADA | 1992 | 475264450.356257 CHINA | 1998 | 272927908.909523 CHINA | 1997 | 463670399.27582 CHINA | 1996 | 460245129.572155 CHINA | 1995 | 454729341.674647 CHINA | 1994 | 461679953.13911 CHINA | 1993 | 464517052.435524 CHINA | 1992 | 462667252.363068 EGYPT | 1998 | 265793258.198038 EGYPT | 1997 | 458048567.07464 EGYPT | 1996 | 464659075.834039 EGYPT | 1995 | 458706996.934987 EGYPT | 1994 | 462371776.831773 EGYPT | 1993 | 451255486.560786 EGYPT | 1992 | 456995897.208139 ETHIOPIA | 1998 | 268943171.929627 ETHIOPIA | 1997 | 464277277.367161 ETHIOPIA | 1996 | 462602456.949342 ETHIOPIA | 1995 | 470346366.768311 ETHIOPIA | 1994 | 466708435.368257 ETHIOPIA | 1993 | 467239519.915701 ETHIOPIA | 1992 | 471235217.565543 FRANCE | 1998 | 267739349.832988 FRANCE | 1997 | 449350199.21098 FRANCE | 1996 | 461505476.598774 FRANCE | 1995 | 461539268.735617 FRANCE | 1994 | 459762456.397056 FRANCE | 1993 | 459968702.91268 FRANCE | 1992 | 457534790.12884 GERMANY | 1998 | 281544267.441963 GERMANY | 1997 | 473774636.380414 GERMANY | 1996 | 477324482.140595 GERMANY | 1995 | 475306494.809531 GERMANY | 1994 | 482075909.287014 GERMANY | 1993 | 474069649.282544 GERMANY | 1992 | 469805282.641657 INDIA | 1998 | 269269530.494744 INDIA | 1997 | 473846462.836062 INDIA | 1996 | 474429430.593657 INDIA | 1995 | 469232337.615012 INDIA | 1994 | 475469468.293046 INDIA | 1993 | 471152880.127784 INDIA | 1992 | 482153942.149428 INDONESIA | 1998 | 270002775.3495 INDONESIA | 1997 | 461866268.547908 INDONESIA | 1996 | 469793616.734372 INDONESIA | 1995 | 463685739.103652 INDONESIA | 1994 | 472774675.241634 INDONESIA | 1993 | 465540267.723446 INDONESIA | 1992 | 471995257.852988 IRAN | 1998 | 276489951.064117 IRAN | 1997 | 461880581.456501 IRAN | 1996 | 461780328.197473 IRAN | 1995 | 461718651.311891 IRAN | 1994 | 462224987.754357 IRAN | 1993 | 453777155.654936 IRAN | 1992 | 459273654.770664 IRAQ | 1998 | 272207223.385778 IRAQ | 1997 | 472805306.190449 IRAQ | 1996 | 469008727.186881 IRAQ | 1995 | 471653759.190547 IRAQ | 1994 | 474111914.795046 IRAQ | 1993 | 470095207.974963 IRAQ | 1992 | 473382447.450614 JAPAN | 1998 | 274463854.592014 JAPAN | 1997 | 470686370.50465 JAPAN | 1996 | 467571670.931051 JAPAN | 1995 | 468363253.831753 JAPAN | 1994 | 472004776.284868 JAPAN | 1993 | 471948560.008024 JAPAN | 1992 | 461999872.707211 JORDAN | 1998 | 273509954.0637 JORDAN | 1997 | 462850859.867914 JORDAN | 1996 | 459510923.168501 JORDAN | 1995 | 462207070.504247 JORDAN | 1994 | 466639518.228702 JORDAN | 1993 | 465266842.083544 JORDAN | 1992 | 454595655.571705 KENYA | 1998 | 280166685.213777 KENYA | 1997 | 472447745.088194 KENYA | 1996 | 476668122.401192 KENYA | 1995 | 468847822.677655 KENYA | 1994 | 473966874.585192 KENYA | 1993 | 478195359.479593 KENYA | 1992 | 482699445.196357 MOROCCO | 1998 | 269467080.505444 MOROCCO | 1997 | 467116425.109198 MOROCCO | 1996 | 466855967.306959 MOROCCO | 1995 | 477099010.315663 MOROCCO | 1994 | 472944495.673298 MOROCCO | 1993 | 465133985.469861 MOROCCO | 1992 | 465917536.453991 MOZAMBIQUE | 1998 | 271441281.142058 MOZAMBIQUE | 1997 | 457858486.054773 MOZAMBIQUE | 1996 | 459637826.926872 MOZAMBIQUE | 1995 | 458246015.267214 MOZAMBIQUE | 1994 | 457542312.228177 MOZAMBIQUE | 1993 | 454896534.413638 MOZAMBIQUE | 1992 | 456756847.849331 PERU | 1998 | 276620110.828865 PERU | 1997 | 465064257.61092 PERU | 1996 | 475801591.374049 PERU | 1995 | 465880297.515433 PERU | 1994 | 466719682.416132 PERU | 1993 | 470967301.812141 PERU | 1992 | 460036883.519902 ROMANIA | 1998 | 271347973.733513 ROMANIA | 1997 | 458512916.148204 ROMANIA | 1996 | 464992670.382354 ROMANIA | 1995 | 459385345.414305 ROMANIA | 1994 | 458366342.815056 ROMANIA | 1993 | 459791103.304623 ROMANIA | 1992 | 469396803.42984 RUSSIA | 1998 | 276512559.496453 RUSSIA | 1997 | 475879506.738101 RUSSIA | 1996 | 470230571.413684 RUSSIA | 1995 | 472721071.539607 RUSSIA | 1994 | 475044212.283753 RUSSIA | 1993 | 473109408.410743 RUSSIA | 1992 | 476096695.248786 SAUDI ARABIA | 1998 | 279032254.881596 SAUDI ARABIA | 1997 | 471639059.87541 SAUDI ARABIA | 1996 | 479716225.365644 SAUDI ARABIA | 1995 | 473918945.218367 SAUDI ARABIA | 1994 | 472451828.936606 SAUDI ARABIA | 1993 | 475880521.818065 SAUDI ARABIA | 1992 | 476537349.840748 UNITED KINGDOM | 1998 | 272671674.879323 UNITED KINGDOM | 1997 | 458869171.52926 UNITED KINGDOM | 1996 | 465328033.035376 UNITED KINGDOM | 1995 | 468353031.27494 UNITED KINGDOM | 1994 | 457019276.767756 UNITED KINGDOM | 1993 | 460985053.417584 UNITED KINGDOM | 1992 | 463483021.858378 UNITED STATES | 1998 | 282295397.732048 UNITED STATES | 1997 | 468513098.741278 UNITED STATES | 1996 | 476834123.47058 UNITED STATES | 1995 | 478736675.470471 UNITED STATES | 1994 | 475420289.326372 UNITED STATES | 1993 | 476234458.2881 UNITED STATES | 1992 | 478210745.663593 VIETNAM | 1998 | 270465153.590222 VIETNAM | 1997 | 466172545.373757 VIETNAM | 1996 | 463151088.967982 VIETNAM | 1995 | 454154811.484581 VIETNAM | 1994 | 457566392.13348 VIETNAM | 1993 | 468903688.61288 VIETNAM | 1992 | 465260520.706745 (175 rows) COMMIT; COMMIT