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 '%black%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 261623395.28291 ALGERIA | 1997 | 449325512.343611 ALGERIA | 1996 | 450339088.565517 ALGERIA | 1995 | 447366412.845104 ALGERIA | 1994 | 455550722.714307 ALGERIA | 1993 | 452549637.899369 ALGERIA | 1992 | 449745839.546153 ARGENTINA | 1998 | 279757933.186222 ARGENTINA | 1997 | 479574128.465536 ARGENTINA | 1996 | 471426455.379567 ARGENTINA | 1995 | 471478096.978123 ARGENTINA | 1994 | 474263982.992748 ARGENTINA | 1993 | 467729389.784838 ARGENTINA | 1992 | 487147802.580883 BRAZIL | 1998 | 279571857.607262 BRAZIL | 1997 | 475750718.091263 BRAZIL | 1996 | 471614250.142128 BRAZIL | 1995 | 470815674.928256 BRAZIL | 1994 | 469870620.74501 BRAZIL | 1993 | 471672240.957542 BRAZIL | 1992 | 476658464.455551 CANADA | 1998 | 281583114.304777 CANADA | 1997 | 485500927.915593 CANADA | 1996 | 483256340.311612 CANADA | 1995 | 480610919.920271 CANADA | 1994 | 490088893.088505 CANADA | 1993 | 485236230.213308 CANADA | 1992 | 491784056.458488 CHINA | 1998 | 274125749.859908 CHINA | 1997 | 461437568.769918 CHINA | 1996 | 464303064.327253 CHINA | 1995 | 469814513.059128 CHINA | 1994 | 467516734.210483 CHINA | 1993 | 467062588.034224 CHINA | 1992 | 466310206.148178 EGYPT | 1998 | 271755231.558965 EGYPT | 1997 | 460477380.819565 EGYPT | 1996 | 469519367.25529 EGYPT | 1995 | 464518132.705954 EGYPT | 1994 | 463229009.820002 EGYPT | 1993 | 466309336.79861 EGYPT | 1992 | 459475072.060323 ETHIOPIA | 1998 | 267330513.268688 ETHIOPIA | 1997 | 452014530.576544 ETHIOPIA | 1996 | 445938179.300729 ETHIOPIA | 1995 | 453693825.350304 ETHIOPIA | 1994 | 455765114.513981 ETHIOPIA | 1993 | 456874297.712208 ETHIOPIA | 1992 | 455177702.807451 FRANCE | 1998 | 264512042.314048 FRANCE | 1997 | 455659533.024993 FRANCE | 1996 | 467681062.855326 FRANCE | 1995 | 467313828.762414 FRANCE | 1994 | 465507502.53902 FRANCE | 1993 | 473167894.906095 FRANCE | 1992 | 464249244.103349 GERMANY | 1998 | 275222474.322638 GERMANY | 1997 | 477197633.269676 GERMANY | 1996 | 476963208.945496 GERMANY | 1995 | 468794679.46985 GERMANY | 1994 | 468034423.464245 GERMANY | 1993 | 468505909.042387 GERMANY | 1992 | 461482056.797487 INDIA | 1998 | 280693386.02995 INDIA | 1997 | 474630552.561982 INDIA | 1996 | 473896639.159366 INDIA | 1995 | 469086016.611357 INDIA | 1994 | 474437530.102143 INDIA | 1993 | 473673996.552069 INDIA | 1992 | 482767253.399597 INDONESIA | 1998 | 270413632.557686 INDONESIA | 1997 | 464383430.221555 INDONESIA | 1996 | 450963096.348399 INDONESIA | 1995 | 460950377.739327 INDONESIA | 1994 | 463748413.943087 INDONESIA | 1993 | 466613234.419642 INDONESIA | 1992 | 462045952.583083 IRAN | 1998 | 272042792.664046 IRAN | 1997 | 462187600.577787 IRAN | 1996 | 462787712.310032 IRAN | 1995 | 467173261.747008 IRAN | 1994 | 460089679.590296 IRAN | 1993 | 472243736.314702 IRAN | 1992 | 464460143.707383 IRAQ | 1998 | 271242433.724733 IRAQ | 1997 | 473462489.563172 IRAQ | 1996 | 469603898.514646 IRAQ | 1995 | 473305449.519811 IRAQ | 1994 | 477111040.652895 IRAQ | 1993 | 470813647.259179 IRAQ | 1992 | 473506414.278095 JAPAN | 1998 | 276378676.903921 JAPAN | 1997 | 470356786.630617 JAPAN | 1996 | 469886259.257525 JAPAN | 1995 | 474806787.730832 JAPAN | 1994 | 465048709.369631 JAPAN | 1993 | 468068052.741477 JAPAN | 1992 | 471757053.163924 JORDAN | 1998 | 271326881.618503 JORDAN | 1997 | 456723884.394551 JORDAN | 1996 | 467445029.92162 JORDAN | 1995 | 461291526.015517 JORDAN | 1994 | 468108179.255103 JORDAN | 1993 | 463870012.962868 JORDAN | 1992 | 466536495.246935 KENYA | 1998 | 280752877.252037 KENYA | 1997 | 468997807.201525 KENYA | 1996 | 473204145.063705 KENYA | 1995 | 473210070.343775 KENYA | 1994 | 473849831.777601 KENYA | 1993 | 470506710.624106 KENYA | 1992 | 470788417.106051 MOROCCO | 1998 | 276449666.696589 MOROCCO | 1997 | 463124251.167024 MOROCCO | 1996 | 470136306.243821 MOROCCO | 1995 | 468047561.024231 MOROCCO | 1994 | 473422721.9973 MOROCCO | 1993 | 471531021.13601 MOROCCO | 1992 | 465506227.198459 MOZAMBIQUE | 1998 | 276241750.176484 MOZAMBIQUE | 1997 | 459882416.748057 MOZAMBIQUE | 1996 | 456086641.814798 MOZAMBIQUE | 1995 | 461479757.511329 MOZAMBIQUE | 1994 | 462687362.690567 MOZAMBIQUE | 1993 | 455744033.445195 MOZAMBIQUE | 1992 | 458030997.854314 PERU | 1998 | 270093096.027047 PERU | 1997 | 471111902.502432 PERU | 1996 | 463006841.257991 PERU | 1995 | 467914540.163153 PERU | 1994 | 462622205.956485 PERU | 1993 | 466349915.915664 PERU | 1992 | 475097477.783911 ROMANIA | 1998 | 281331410.64507 ROMANIA | 1997 | 462967155.075114 ROMANIA | 1996 | 480685229.058569 ROMANIA | 1995 | 473664304.687976 ROMANIA | 1994 | 474768137.075705 ROMANIA | 1993 | 469537133.490886 ROMANIA | 1992 | 473556860.943628 RUSSIA | 1998 | 282870112.280787 RUSSIA | 1997 | 470689178.518447 RUSSIA | 1996 | 473156059.056619 RUSSIA | 1995 | 479582884.168331 RUSSIA | 1994 | 471602385.627629 RUSSIA | 1993 | 471654850.126267 RUSSIA | 1992 | 476978196.333518 SAUDI ARABIA | 1998 | 270320921.567552 SAUDI ARABIA | 1997 | 463223703.645336 SAUDI ARABIA | 1996 | 473293199.520461 SAUDI ARABIA | 1995 | 470649775.171037 SAUDI ARABIA | 1994 | 465080273.00923 SAUDI ARABIA | 1993 | 467876452.147245 SAUDI ARABIA | 1992 | 464368584.684168 UNITED KINGDOM | 1998 | 272935965.646488 UNITED KINGDOM | 1997 | 465935347.054836 UNITED KINGDOM | 1996 | 459382029.456999 UNITED KINGDOM | 1995 | 464108400.280868 UNITED KINGDOM | 1994 | 462522047.301025 UNITED KINGDOM | 1993 | 463987950.196056 UNITED KINGDOM | 1992 | 459181777.662161 UNITED STATES | 1998 | 274976579.74011 UNITED STATES | 1997 | 468666421.165847 UNITED STATES | 1996 | 465885805.089702 UNITED STATES | 1995 | 469755998.463388 UNITED STATES | 1994 | 479922734.043849 UNITED STATES | 1993 | 473881387.240804 UNITED STATES | 1992 | 468246118.627788 VIETNAM | 1998 | 278618066.572371 VIETNAM | 1997 | 462429237.082177 VIETNAM | 1996 | 472702970.865018 VIETNAM | 1995 | 468508625.746585 VIETNAM | 1994 | 468053051.319895 VIETNAM | 1993 | 470075802.859895 VIETNAM | 1992 | 467530501.843286 (175 rows) COMMIT; COMMIT