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 | 272125991.137998 ALGERIA | 1997 | 457231500.437512 ALGERIA | 1996 | 458564606.771168 ALGERIA | 1995 | 462936414.15038 ALGERIA | 1994 | 464953737.705304 ALGERIA | 1993 | 458231510.65371 ALGERIA | 1992 | 466099161.555313 ARGENTINA | 1998 | 283756353.268236 ARGENTINA | 1997 | 477736520.423485 ARGENTINA | 1996 | 481206499.329542 ARGENTINA | 1995 | 472277310.126429 ARGENTINA | 1994 | 473680452.957369 ARGENTINA | 1993 | 466882208.097207 ARGENTINA | 1992 | 477446931.806584 BRAZIL | 1998 | 272444455.513508 BRAZIL | 1997 | 474367266.00801 BRAZIL | 1996 | 475747809.653694 BRAZIL | 1995 | 465878765.332083 BRAZIL | 1994 | 465386167.65222 BRAZIL | 1993 | 474664252.971581 BRAZIL | 1992 | 474123656.505949 CANADA | 1998 | 281212434.300331 CANADA | 1997 | 474627695.283018 CANADA | 1996 | 476266243.345001 CANADA | 1995 | 481157706.205827 CANADA | 1994 | 484983127.71861 CANADA | 1993 | 475089084.80215 CANADA | 1992 | 481580242.489711 CHINA | 1998 | 277119443.460109 CHINA | 1997 | 469611958.998337 CHINA | 1996 | 474069758.44276 CHINA | 1995 | 469654426.814855 CHINA | 1994 | 460045501.846148 CHINA | 1993 | 471551846.096142 CHINA | 1992 | 474046666.82822 EGYPT | 1998 | 264279836.242388 EGYPT | 1997 | 460287154.522094 EGYPT | 1996 | 460512542.113847 EGYPT | 1995 | 458417082.338082 EGYPT | 1994 | 464983003.905875 EGYPT | 1993 | 459917016.529429 EGYPT | 1992 | 455551142.688901 ETHIOPIA | 1998 | 282247151.335663 ETHIOPIA | 1997 | 458992680.848076 ETHIOPIA | 1996 | 461656618.732229 ETHIOPIA | 1995 | 467092587.485627 ETHIOPIA | 1994 | 466589328.929963 ETHIOPIA | 1993 | 470733420.006956 ETHIOPIA | 1992 | 458952715.058143 FRANCE | 1998 | 271574589.428207 FRANCE | 1997 | 467399188.896686 FRANCE | 1996 | 466548481.047401 FRANCE | 1995 | 460841873.616147 FRANCE | 1994 | 462854147.962549 FRANCE | 1993 | 462043951.391313 FRANCE | 1992 | 465874150.957418 GERMANY | 1998 | 279001537.393614 GERMANY | 1997 | 477178792.026164 GERMANY | 1996 | 474521509.364345 GERMANY | 1995 | 481255971.060878 GERMANY | 1994 | 481052367.304993 GERMANY | 1993 | 480084254.207427 GERMANY | 1992 | 469832439.271089 INDIA | 1998 | 272348104.145872 INDIA | 1997 | 466842452.206716 INDIA | 1996 | 479320038.516363 INDIA | 1995 | 472486165.909782 INDIA | 1994 | 473135796.82639 INDIA | 1993 | 468519158.683665 INDIA | 1992 | 472096142.182606 INDONESIA | 1998 | 269686359.661788 INDONESIA | 1997 | 457870274.871046 INDONESIA | 1996 | 469219221.976703 INDONESIA | 1995 | 458735061.203945 INDONESIA | 1994 | 450560960.435106 INDONESIA | 1993 | 464177103.357523 INDONESIA | 1992 | 457119781.335709 IRAN | 1998 | 273548283.019767 IRAN | 1997 | 462743831.949013 IRAN | 1996 | 463191304.405196 IRAN | 1995 | 472570198.795456 IRAN | 1994 | 466659612.050532 IRAN | 1993 | 462481981.286253 IRAN | 1992 | 457350938.457579 IRAQ | 1998 | 273912192.674926 IRAQ | 1997 | 458449117.578247 IRAQ | 1996 | 468630488.289031 IRAQ | 1995 | 463836290.628678 IRAQ | 1994 | 464921933.039307 IRAQ | 1993 | 465230598.81934 IRAQ | 1992 | 473441032.193379 JAPAN | 1998 | 274750308.787119 JAPAN | 1997 | 471094033.495078 JAPAN | 1996 | 468141760.939628 JAPAN | 1995 | 463504168.957867 JAPAN | 1994 | 472761352.912729 JAPAN | 1993 | 470941784.115902 JAPAN | 1992 | 462207112.091986 JORDAN | 1998 | 268953045.027268 JORDAN | 1997 | 464851181.437936 JORDAN | 1996 | 464405064.938075 JORDAN | 1995 | 461816715.748357 JORDAN | 1994 | 465582357.730144 JORDAN | 1993 | 461609407.566778 JORDAN | 1992 | 462319883.038721 KENYA | 1998 | 277357578.880056 KENYA | 1997 | 471771913.305975 KENYA | 1996 | 470868966.658442 KENYA | 1995 | 471908995.604226 KENYA | 1994 | 469999473.437169 KENYA | 1993 | 470150527.393123 KENYA | 1992 | 474609583.792485 MOROCCO | 1998 | 276546452.596615 MOROCCO | 1997 | 473371916.865734 MOROCCO | 1996 | 471974846.145099 MOROCCO | 1995 | 478182437.810023 MOROCCO | 1994 | 466537760.391912 MOROCCO | 1993 | 472032249.321561 MOROCCO | 1992 | 477157615.247098 MOZAMBIQUE | 1998 | 276467865.859691 MOZAMBIQUE | 1997 | 463161907.952395 MOZAMBIQUE | 1996 | 464462193.30644 MOZAMBIQUE | 1995 | 464207160.530367 MOZAMBIQUE | 1994 | 463139199.705615 MOZAMBIQUE | 1993 | 470952241.107975 MOZAMBIQUE | 1992 | 465075656.83508 PERU | 1998 | 275020528.425878 PERU | 1997 | 466138316.447711 PERU | 1996 | 469850041.637557 PERU | 1995 | 456702306.99937 PERU | 1994 | 466661470.196614 PERU | 1993 | 460123965.508981 PERU | 1992 | 463198860.930482 ROMANIA | 1998 | 272988562.744046 ROMANIA | 1997 | 467520020.73857 ROMANIA | 1996 | 470721097.710658 ROMANIA | 1995 | 468607431.140641 ROMANIA | 1994 | 455999474.15313 ROMANIA | 1993 | 457883488.738044 ROMANIA | 1992 | 473595295.446384 RUSSIA | 1998 | 272198733.22626 RUSSIA | 1997 | 468656583.817102 RUSSIA | 1996 | 459912307.114511 RUSSIA | 1995 | 467661436.24437 RUSSIA | 1994 | 467970876.565556 RUSSIA | 1993 | 466761798.399516 RUSSIA | 1992 | 468377216.990212 SAUDI ARABIA | 1998 | 275425932.75947 SAUDI ARABIA | 1997 | 475185834.729454 SAUDI ARABIA | 1996 | 471190890.805333 SAUDI ARABIA | 1995 | 467703343.109582 SAUDI ARABIA | 1994 | 467044737.041872 SAUDI ARABIA | 1993 | 476579701.0877 SAUDI ARABIA | 1992 | 477801640.197848 UNITED KINGDOM | 1998 | 274562324.25483 UNITED KINGDOM | 1997 | 465376029.840511 UNITED KINGDOM | 1996 | 460220500.662104 UNITED KINGDOM | 1995 | 461082614.478307 UNITED KINGDOM | 1994 | 460783752.119411 UNITED KINGDOM | 1993 | 466896310.024165 UNITED KINGDOM | 1992 | 461503348.559238 UNITED STATES | 1998 | 271163941.639073 UNITED STATES | 1997 | 475707094.207217 UNITED STATES | 1996 | 481410733.552095 UNITED STATES | 1995 | 478819360.299392 UNITED STATES | 1994 | 476515265.886365 UNITED STATES | 1993 | 468063258.903208 UNITED STATES | 1992 | 472935384.234472 VIETNAM | 1998 | 271601045.421141 VIETNAM | 1997 | 458200854.593915 VIETNAM | 1996 | 473580162.214687 VIETNAM | 1995 | 459474460.642493 VIETNAM | 1994 | 466912967.525642 VIETNAM | 1993 | 462234722.717269 VIETNAM | 1992 | 463137698.981641 (175 rows) COMMIT; COMMIT