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 '%dark%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 265405009.226782 ALGERIA | 1997 | 442725520.096151 ALGERIA | 1996 | 452543950.736064 ALGERIA | 1995 | 446673362.207808 ALGERIA | 1994 | 452595554.846553 ALGERIA | 1993 | 447580923.739411 ALGERIA | 1992 | 452564550.333569 ARGENTINA | 1998 | 269488906.662635 ARGENTINA | 1997 | 466893139.188582 ARGENTINA | 1996 | 468814380.256816 ARGENTINA | 1995 | 460891019.156214 ARGENTINA | 1994 | 466034115.927804 ARGENTINA | 1993 | 472690377.212963 ARGENTINA | 1992 | 462974414.354224 BRAZIL | 1998 | 268740183.323412 BRAZIL | 1997 | 463489733.191699 BRAZIL | 1996 | 469106332.758612 BRAZIL | 1995 | 467729127.173584 BRAZIL | 1994 | 479338302.798501 BRAZIL | 1993 | 467684496.385065 BRAZIL | 1992 | 467056976.214276 CANADA | 1998 | 279467919.347656 CANADA | 1997 | 464373713.49934 CANADA | 1996 | 473123339.819813 CANADA | 1995 | 471635632.276418 CANADA | 1994 | 485888265.453974 CANADA | 1993 | 476391798.253958 CANADA | 1992 | 471026759.777046 CHINA | 1998 | 277572603.983333 CHINA | 1997 | 474840206.038272 CHINA | 1996 | 472275840.258882 CHINA | 1995 | 474330821.933645 CHINA | 1994 | 480281046.414499 CHINA | 1993 | 473210348.515889 CHINA | 1992 | 474369628.240327 EGYPT | 1998 | 272699215.184214 EGYPT | 1997 | 464139341.426994 EGYPT | 1996 | 468998335.686784 EGYPT | 1995 | 454485833.569399 EGYPT | 1994 | 454057382.697696 EGYPT | 1993 | 456895716.480215 EGYPT | 1992 | 460631477.804037 ETHIOPIA | 1998 | 268259226.276806 ETHIOPIA | 1997 | 462524738.147524 ETHIOPIA | 1996 | 464058210.439911 ETHIOPIA | 1995 | 461029308.153727 ETHIOPIA | 1994 | 462667142.472139 ETHIOPIA | 1993 | 459655908.303258 ETHIOPIA | 1992 | 465023839.501428 FRANCE | 1998 | 270364172.221908 FRANCE | 1997 | 455021435.593369 FRANCE | 1996 | 458779560.553382 FRANCE | 1995 | 451469128.915156 FRANCE | 1994 | 467149331.628811 FRANCE | 1993 | 455229953.859245 FRANCE | 1992 | 464479465.365451 GERMANY | 1998 | 272019307.762812 GERMANY | 1997 | 472260534.259716 GERMANY | 1996 | 476382102.86442 GERMANY | 1995 | 472290367.834929 GERMANY | 1994 | 469050263.051293 GERMANY | 1993 | 476575398.062032 GERMANY | 1992 | 475313694.073534 INDIA | 1998 | 278627946.079488 INDIA | 1997 | 469930933.978489 INDIA | 1996 | 476541379.4427 INDIA | 1995 | 471892989.812823 INDIA | 1994 | 473417284.700981 INDIA | 1993 | 476852358.69447 INDIA | 1992 | 473842338.451849 INDONESIA | 1998 | 268772064.017082 INDONESIA | 1997 | 456241560.148654 INDONESIA | 1996 | 459795624.135423 INDONESIA | 1995 | 461911569.615677 INDONESIA | 1994 | 457499213.178192 INDONESIA | 1993 | 460230157.428046 INDONESIA | 1992 | 453313222.075194 IRAN | 1998 | 270286689.059085 IRAN | 1997 | 462820712.767142 IRAN | 1996 | 460101457.876559 IRAN | 1995 | 460408189.314692 IRAN | 1994 | 450136408.700524 IRAN | 1993 | 454971290.543932 IRAN | 1992 | 457923634.846717 IRAQ | 1998 | 277738792.136948 IRAQ | 1997 | 472563254.512882 IRAQ | 1996 | 481502641.262297 IRAQ | 1995 | 478064804.788681 IRAQ | 1994 | 476810214.136363 IRAQ | 1993 | 479413697.677185 IRAQ | 1992 | 465153878.486761 JAPAN | 1998 | 273536437.821761 JAPAN | 1997 | 464675176.151954 JAPAN | 1996 | 461865051.96326 JAPAN | 1995 | 468971025.634877 JAPAN | 1994 | 466708741.382609 JAPAN | 1993 | 462819780.348177 JAPAN | 1992 | 456900518.385586 JORDAN | 1998 | 267124038.625292 JORDAN | 1997 | 452343470.34493 JORDAN | 1996 | 462345277.902045 JORDAN | 1995 | 456004057.210161 JORDAN | 1994 | 455099764.154524 JORDAN | 1993 | 461894694.745517 JORDAN | 1992 | 462773840.319416 KENYA | 1998 | 279383884.898463 KENYA | 1997 | 471584104.673453 KENYA | 1996 | 473667656.626509 KENYA | 1995 | 470770926.947444 KENYA | 1994 | 475372819.797878 KENYA | 1993 | 484532243.007102 KENYA | 1992 | 473569987.695089 MOROCCO | 1998 | 269758933.238099 MOROCCO | 1997 | 469870112.944073 MOROCCO | 1996 | 473641736.104689 MOROCCO | 1995 | 466187866.680383 MOROCCO | 1994 | 466928903.434852 MOROCCO | 1993 | 464142107.622983 MOROCCO | 1992 | 475592148.405994 MOZAMBIQUE | 1998 | 273457626.228256 MOZAMBIQUE | 1997 | 456769755.236535 MOZAMBIQUE | 1996 | 463036744.650211 MOZAMBIQUE | 1995 | 461174701.769992 MOZAMBIQUE | 1994 | 461242651.658267 MOZAMBIQUE | 1993 | 451088916.236406 MOZAMBIQUE | 1992 | 455694568.967228 PERU | 1998 | 270709173.794411 PERU | 1997 | 473036170.459987 PERU | 1996 | 461753510.967274 PERU | 1995 | 467120314.302222 PERU | 1994 | 459112927.578697 PERU | 1993 | 467147224.497011 PERU | 1992 | 468582269.584782 ROMANIA | 1998 | 271478294.532655 ROMANIA | 1997 | 466002947.049986 ROMANIA | 1996 | 463709454.394475 ROMANIA | 1995 | 460577347.188869 ROMANIA | 1994 | 468432363.163632 ROMANIA | 1993 | 460230351.950403 ROMANIA | 1992 | 463969426.627418 RUSSIA | 1998 | 275057613.254128 RUSSIA | 1997 | 458035179.322304 RUSSIA | 1996 | 467922621.176907 RUSSIA | 1995 | 459537411.498806 RUSSIA | 1994 | 460334118.769558 RUSSIA | 1993 | 458622318.390112 RUSSIA | 1992 | 469011170.902804 SAUDI ARABIA | 1998 | 281085173.79455 SAUDI ARABIA | 1997 | 469725957.162774 SAUDI ARABIA | 1996 | 477385346.503801 SAUDI ARABIA | 1995 | 481530557.353735 SAUDI ARABIA | 1994 | 472393167.144467 SAUDI ARABIA | 1993 | 479235372.303486 SAUDI ARABIA | 1992 | 481470268.74114 UNITED KINGDOM | 1998 | 266022591.378277 UNITED KINGDOM | 1997 | 461933917.756529 UNITED KINGDOM | 1996 | 460592891.061575 UNITED KINGDOM | 1995 | 450083817.592538 UNITED KINGDOM | 1994 | 453794797.239928 UNITED KINGDOM | 1993 | 457903486.783764 UNITED KINGDOM | 1992 | 462450053.60321 UNITED STATES | 1998 | 271223960.923327 UNITED STATES | 1997 | 471891327.606685 UNITED STATES | 1996 | 468393863.363351 UNITED STATES | 1995 | 467215377.248666 UNITED STATES | 1994 | 477907056.806028 UNITED STATES | 1993 | 468604097.625061 UNITED STATES | 1992 | 470987916.975843 VIETNAM | 1998 | 270016227.042947 VIETNAM | 1997 | 463650970.69698 VIETNAM | 1996 | 467770345.67986 VIETNAM | 1995 | 461314502.76353 VIETNAM | 1994 | 460728162.736144 VIETNAM | 1993 | 457442826.568662 VIETNAM | 1992 | 456776609.824849 (175 rows) COMMIT; COMMIT