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 '%azure%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 272323816.160586 ALGERIA | 1997 | 457557516.699738 ALGERIA | 1996 | 459447408.99272 ALGERIA | 1995 | 463358374.896266 ALGERIA | 1994 | 465041379.021913 ALGERIA | 1993 | 458850576.837813 ALGERIA | 1992 | 466595436.955342 ARGENTINA | 1998 | 284196176.811259 ARGENTINA | 1997 | 478134611.904771 ARGENTINA | 1996 | 481848086.26231 ARGENTINA | 1995 | 472613713.492999 ARGENTINA | 1994 | 473638244.702182 ARGENTINA | 1993 | 467783549.441979 ARGENTINA | 1992 | 477561385.47768 BRAZIL | 1998 | 272154520.54275 BRAZIL | 1997 | 475003112.489983 BRAZIL | 1996 | 476447019.992116 BRAZIL | 1995 | 466032663.837537 BRAZIL | 1994 | 465596807.039673 BRAZIL | 1993 | 474895321.751051 BRAZIL | 1992 | 473939074.328825 CANADA | 1998 | 281319589.602038 CANADA | 1997 | 475273575.756421 CANADA | 1996 | 476917833.674098 CANADA | 1995 | 481257683.857506 CANADA | 1994 | 485332684.209532 CANADA | 1993 | 475219055.368133 CANADA | 1992 | 482098067.510174 CHINA | 1998 | 277193219.753606 CHINA | 1997 | 469556953.879157 CHINA | 1996 | 474335062.901265 CHINA | 1995 | 469923240.247817 CHINA | 1994 | 460657390.140252 CHINA | 1993 | 471770811.10576 CHINA | 1992 | 475245561.111827 EGYPT | 1998 | 264358739.664947 EGYPT | 1997 | 460808755.112564 EGYPT | 1996 | 460782531.092478 EGYPT | 1995 | 458860530.095154 EGYPT | 1994 | 465206662.245002 EGYPT | 1993 | 460593832.825319 EGYPT | 1992 | 455757689.857743 ETHIOPIA | 1998 | 282499964.115665 ETHIOPIA | 1997 | 459980763.229477 ETHIOPIA | 1996 | 462312697.067013 ETHIOPIA | 1995 | 467772192.445744 ETHIOPIA | 1994 | 466853650.899932 ETHIOPIA | 1993 | 470747393.758115 ETHIOPIA | 1992 | 459150555.087089 FRANCE | 1998 | 271682597.949267 FRANCE | 1997 | 467773539.61687 FRANCE | 1996 | 467092110.990416 FRANCE | 1995 | 461635588.974859 FRANCE | 1994 | 463276369.818498 FRANCE | 1993 | 462642358.706832 FRANCE | 1992 | 466331621.88565 GERMANY | 1998 | 279271128.85491 GERMANY | 1997 | 477872252.803485 GERMANY | 1996 | 474771214.465881 GERMANY | 1995 | 482078975.813381 GERMANY | 1994 | 481541892.767856 GERMANY | 1993 | 480214084.716218 GERMANY | 1992 | 470668273.76047 INDIA | 1998 | 272864782.289213 INDIA | 1997 | 467568696.163168 INDIA | 1996 | 480731339.087171 INDIA | 1995 | 473245489.045379 INDIA | 1994 | 474010072.50435 INDIA | 1993 | 469058654.754476 INDIA | 1992 | 472385122.427129 INDONESIA | 1998 | 269763215.754276 INDONESIA | 1997 | 458676254.223333 INDONESIA | 1996 | 470176559.492194 INDONESIA | 1995 | 458987962.108234 INDONESIA | 1994 | 450886293.85055 INDONESIA | 1993 | 464284423.825609 INDONESIA | 1992 | 457810460.346374 IRAN | 1998 | 273831558.229876 IRAN | 1997 | 463022578.479042 IRAN | 1996 | 463284159.141349 IRAN | 1995 | 473001130.119133 IRAN | 1994 | 467128267.240143 IRAN | 1993 | 463072624.777677 IRAN | 1992 | 457624548.776108 IRAQ | 1998 | 274106841.824774 IRAQ | 1997 | 459104315.642754 IRAQ | 1996 | 469065376.980566 IRAQ | 1995 | 464714990.365922 IRAQ | 1994 | 465303272.166612 IRAQ | 1993 | 466250713.762274 IRAQ | 1992 | 474055299.461862 JAPAN | 1998 | 274727639.700973 JAPAN | 1997 | 471609689.555875 JAPAN | 1996 | 468934491.973425 JAPAN | 1995 | 464567039.176286 JAPAN | 1994 | 472505721.052839 JAPAN | 1993 | 471762106.486381 JAPAN | 1992 | 462532100.175243 JORDAN | 1998 | 269171606.208189 JORDAN | 1997 | 466166246.569451 JORDAN | 1996 | 464990398.967187 JORDAN | 1995 | 461984541.527555 JORDAN | 1994 | 466228166.781555 JORDAN | 1993 | 462333342.762084 JORDAN | 1992 | 462891645.426683 KENYA | 1998 | 277659414.108519 KENYA | 1997 | 472633620.088696 KENYA | 1996 | 471784027.86177 KENYA | 1995 | 472458288.961222 KENYA | 1994 | 470215643.216468 KENYA | 1993 | 471016360.931227 KENYA | 1992 | 474691379.779624 MOROCCO | 1998 | 276381724.576031 MOROCCO | 1997 | 474149973.297128 MOROCCO | 1996 | 472274630.039843 MOROCCO | 1995 | 478188199.091632 MOROCCO | 1994 | 466379356.893893 MOROCCO | 1993 | 472559725.250624 MOROCCO | 1992 | 477494854.452779 MOZAMBIQUE | 1998 | 276880260.898676 MOZAMBIQUE | 1997 | 463931668.830257 MOZAMBIQUE | 1996 | 464738010.649871 MOZAMBIQUE | 1995 | 464014532.483167 MOZAMBIQUE | 1994 | 463816772.074508 MOZAMBIQUE | 1993 | 471331677.266252 MOZAMBIQUE | 1992 | 465063212.131939 PERU | 1998 | 275326906.794665 PERU | 1997 | 466326863.677139 PERU | 1996 | 469969091.08908 PERU | 1995 | 456890678.875183 PERU | 1994 | 467059604.587 PERU | 1993 | 460486712.846273 PERU | 1992 | 463494562.163387 ROMANIA | 1998 | 273093962.42679 ROMANIA | 1997 | 468183946.524255 ROMANIA | 1996 | 471381353.82527 ROMANIA | 1995 | 469224518.374119 ROMANIA | 1994 | 455913867.574711 ROMANIA | 1993 | 458030604.479418 ROMANIA | 1992 | 473883096.38977 RUSSIA | 1998 | 272519913.004085 RUSSIA | 1997 | 468723623.520746 RUSSIA | 1996 | 460333021.754992 RUSSIA | 1995 | 468025901.736024 RUSSIA | 1994 | 468482641.617108 RUSSIA | 1993 | 467091091.862418 RUSSIA | 1992 | 469216774.335392 SAUDI ARABIA | 1998 | 275895655.080811 SAUDI ARABIA | 1997 | 475922874.964362 SAUDI ARABIA | 1996 | 472003419.563443 SAUDI ARABIA | 1995 | 468485100.872402 SAUDI ARABIA | 1994 | 467224720.708724 SAUDI ARABIA | 1993 | 476502879.838622 SAUDI ARABIA | 1992 | 477699724.561489 UNITED KINGDOM | 1998 | 275019466.06628 UNITED KINGDOM | 1997 | 465644403.875772 UNITED KINGDOM | 1996 | 461143031.053676 UNITED KINGDOM | 1995 | 461748452.403953 UNITED KINGDOM | 1994 | 461358855.757436 UNITED KINGDOM | 1993 | 467108720.060168 UNITED KINGDOM | 1992 | 462345271.245628 UNITED STATES | 1998 | 271756440.26112 UNITED STATES | 1997 | 476011564.838504 UNITED STATES | 1996 | 481786059.613065 UNITED STATES | 1995 | 479384007.83938 UNITED STATES | 1994 | 476972009.628503 UNITED STATES | 1993 | 468475130.63538 UNITED STATES | 1992 | 473869682.104889 VIETNAM | 1998 | 271970521.87365 VIETNAM | 1997 | 459083246.122821 VIETNAM | 1996 | 473935647.445445 VIETNAM | 1995 | 459732673.407792 VIETNAM | 1994 | 467508326.952163 VIETNAM | 1993 | 463163389.885067 VIETNAM | 1992 | 463942776.884454 (175 rows) COMMIT; COMMIT