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.933807 ALGERIA | 1997 | 453239464.80076 ALGERIA | 1996 | 453922029.61462 ALGERIA | 1995 | 446438387.500575 ALGERIA | 1994 | 451220811.452904 ALGERIA | 1993 | 451840748.047218 ALGERIA | 1992 | 455180402.705048 ARGENTINA | 1998 | 273864622.116908 ARGENTINA | 1997 | 469584629.926363 ARGENTINA | 1996 | 478154000.308345 ARGENTINA | 1995 | 471096933.723108 ARGENTINA | 1994 | 465109808.514606 ARGENTINA | 1993 | 471186518.017657 ARGENTINA | 1992 | 466501085.272608 BRAZIL | 1998 | 272814245.305871 BRAZIL | 1997 | 470162632.503915 BRAZIL | 1996 | 462192829.195083 BRAZIL | 1995 | 463778056.289605 BRAZIL | 1994 | 466572021.127219 BRAZIL | 1993 | 465661476.189751 BRAZIL | 1992 | 478523298.49822 CANADA | 1998 | 277887685.848925 CANADA | 1997 | 476115128.283864 CANADA | 1996 | 476541875.192749 CANADA | 1995 | 478374531.656026 CANADA | 1994 | 479565897.320905 CANADA | 1993 | 469487038.789258 CANADA | 1992 | 475264450.356256 CHINA | 1998 | 272927908.909524 CHINA | 1997 | 463670399.275819 CHINA | 1996 | 460245129.572156 CHINA | 1995 | 454729341.674648 CHINA | 1994 | 461679953.13911 CHINA | 1993 | 464517052.435525 CHINA | 1992 | 462667252.363067 EGYPT | 1998 | 265793258.198037 EGYPT | 1997 | 458048567.074639 EGYPT | 1996 | 464659075.834038 EGYPT | 1995 | 458706996.934985 EGYPT | 1994 | 462371776.831773 EGYPT | 1993 | 451255486.560787 EGYPT | 1992 | 456995897.208138 ETHIOPIA | 1998 | 268943171.929627 ETHIOPIA | 1997 | 464277277.367165 ETHIOPIA | 1996 | 462602456.949342 ETHIOPIA | 1995 | 470346366.76831 ETHIOPIA | 1994 | 466708435.368253 ETHIOPIA | 1993 | 467239519.915699 ETHIOPIA | 1992 | 471235217.565543 FRANCE | 1998 | 267739349.832988 FRANCE | 1997 | 449350199.21098 FRANCE | 1996 | 461505476.598773 FRANCE | 1995 | 461539268.73562 FRANCE | 1994 | 459762456.397057 FRANCE | 1993 | 459968702.912683 FRANCE | 1992 | 457534790.128841 GERMANY | 1998 | 281544267.441963 GERMANY | 1997 | 473774636.380415 GERMANY | 1996 | 477324482.140591 GERMANY | 1995 | 475306494.809531 GERMANY | 1994 | 482075909.287014 GERMANY | 1993 | 474069649.282546 GERMANY | 1992 | 469805282.641657 INDIA | 1998 | 269269530.494745 INDIA | 1997 | 473846462.836062 INDIA | 1996 | 474429430.593657 INDIA | 1995 | 469232337.615011 INDIA | 1994 | 475469468.293046 INDIA | 1993 | 471152880.127783 INDIA | 1992 | 482153942.149431 INDONESIA | 1998 | 270002775.349502 INDONESIA | 1997 | 461866268.547906 INDONESIA | 1996 | 469793616.734371 INDONESIA | 1995 | 463685739.103656 INDONESIA | 1994 | 472774675.241636 INDONESIA | 1993 | 465540267.723446 INDONESIA | 1992 | 471995257.852987 IRAN | 1998 | 276489951.064117 IRAN | 1997 | 461880581.4565 IRAN | 1996 | 461780328.197475 IRAN | 1995 | 461718651.311894 IRAN | 1994 | 462224987.75436 IRAN | 1993 | 453777155.654934 IRAN | 1992 | 459273654.770665 IRAQ | 1998 | 272207223.385777 IRAQ | 1997 | 472805306.190451 IRAQ | 1996 | 469008727.186877 IRAQ | 1995 | 471653759.190545 IRAQ | 1994 | 474111914.795049 IRAQ | 1993 | 470095207.974959 IRAQ | 1992 | 473382447.450618 JAPAN | 1998 | 274463854.592013 JAPAN | 1997 | 470686370.504648 JAPAN | 1996 | 467571670.93105 JAPAN | 1995 | 468363253.831753 JAPAN | 1994 | 472004776.284869 JAPAN | 1993 | 471948560.008027 JAPAN | 1992 | 461999872.707207 JORDAN | 1998 | 273509954.063701 JORDAN | 1997 | 462850859.867912 JORDAN | 1996 | 459510923.168502 JORDAN | 1995 | 462207070.504247 JORDAN | 1994 | 466639518.228701 JORDAN | 1993 | 465266842.08354 JORDAN | 1992 | 454595655.571703 KENYA | 1998 | 280166685.213776 KENYA | 1997 | 472447745.088192 KENYA | 1996 | 476668122.401194 KENYA | 1995 | 468847822.677654 KENYA | 1994 | 473966874.585193 KENYA | 1993 | 478195359.479593 KENYA | 1992 | 482699445.196354 MOROCCO | 1998 | 269467080.505444 MOROCCO | 1997 | 467116425.109197 MOROCCO | 1996 | 466855967.306957 MOROCCO | 1995 | 477099010.315666 MOROCCO | 1994 | 472944495.673296 MOROCCO | 1993 | 465133985.469862 MOROCCO | 1992 | 465917536.453991 MOZAMBIQUE | 1998 | 271441281.142057 MOZAMBIQUE | 1997 | 457858486.054773 MOZAMBIQUE | 1996 | 459637826.926874 MOZAMBIQUE | 1995 | 458246015.267214 MOZAMBIQUE | 1994 | 457542312.228178 MOZAMBIQUE | 1993 | 454896534.41364 MOZAMBIQUE | 1992 | 456756847.849328 PERU | 1998 | 276620110.828866 PERU | 1997 | 465064257.610923 PERU | 1996 | 475801591.374046 PERU | 1995 | 465880297.515436 PERU | 1994 | 466719682.416134 PERU | 1993 | 470967301.81214 PERU | 1992 | 460036883.5199 ROMANIA | 1998 | 271347973.733512 ROMANIA | 1997 | 458512916.148204 ROMANIA | 1996 | 464992670.382355 ROMANIA | 1995 | 459385345.414307 ROMANIA | 1994 | 458366342.815056 ROMANIA | 1993 | 459791103.304624 ROMANIA | 1992 | 469396803.429836 RUSSIA | 1998 | 276512559.496452 RUSSIA | 1997 | 475879506.738103 RUSSIA | 1996 | 470230571.413686 RUSSIA | 1995 | 472721071.539608 RUSSIA | 1994 | 475044212.283754 RUSSIA | 1993 | 473109408.410743 RUSSIA | 1992 | 476096695.248784 SAUDI ARABIA | 1998 | 279032254.881595 SAUDI ARABIA | 1997 | 471639059.875411 SAUDI ARABIA | 1996 | 479716225.365642 SAUDI ARABIA | 1995 | 473918945.218367 SAUDI ARABIA | 1994 | 472451828.936607 SAUDI ARABIA | 1993 | 475880521.818067 SAUDI ARABIA | 1992 | 476537349.840746 UNITED KINGDOM | 1998 | 272671674.879322 UNITED KINGDOM | 1997 | 458869171.52926 UNITED KINGDOM | 1996 | 465328033.035379 UNITED KINGDOM | 1995 | 468353031.274939 UNITED KINGDOM | 1994 | 457019276.767757 UNITED KINGDOM | 1993 | 460985053.417582 UNITED KINGDOM | 1992 | 463483021.858376 UNITED STATES | 1998 | 282295397.732048 UNITED STATES | 1997 | 468513098.741279 UNITED STATES | 1996 | 476834123.470582 UNITED STATES | 1995 | 478736675.470466 UNITED STATES | 1994 | 475420289.326372 UNITED STATES | 1993 | 476234458.288099 UNITED STATES | 1992 | 478210745.663592 VIETNAM | 1998 | 270465153.590221 VIETNAM | 1997 | 466172545.373755 VIETNAM | 1996 | 463151088.96798 VIETNAM | 1995 | 454154811.484581 VIETNAM | 1994 | 457566392.13348 VIETNAM | 1993 | 468903688.612878 VIETNAM | 1992 | 465260520.706743 (175 rows) COMMIT; COMMIT