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 '%yellow%' ) as profit group by nation, o_year order by nation, o_year desc; nation | o_year | sum_profit ---------------------------+--------+------------------ ALGERIA | 1998 | 270485290.990231 ALGERIA | 1997 | 471274694.809003 ALGERIA | 1996 | 465589368.937629 ALGERIA | 1995 | 465051727.170309 ALGERIA | 1994 | 464587154.953596 ALGERIA | 1993 | 464865648.780021 ALGERIA | 1992 | 464601096.17209 ARGENTINA | 1998 | 275586058.23935 ARGENTINA | 1997 | 471362288.118503 ARGENTINA | 1996 | 478359557.737485 ARGENTINA | 1995 | 473020565.983503 ARGENTINA | 1994 | 468816239.391137 ARGENTINA | 1993 | 466796998.281626 ARGENTINA | 1992 | 474328630.340922 BRAZIL | 1998 | 279902546.231881 BRAZIL | 1997 | 456890994.804783 BRAZIL | 1996 | 463606105.941905 BRAZIL | 1995 | 459617690.707967 BRAZIL | 1994 | 467351565.422082 BRAZIL | 1993 | 461622953.665919 BRAZIL | 1992 | 468654888.226028 CANADA | 1998 | 270764654.423866 CANADA | 1997 | 483829152.295936 CANADA | 1996 | 478172953.406498 CANADA | 1995 | 476736887.786478 CANADA | 1994 | 481075491.110892 CANADA | 1993 | 474876320.290683 CANADA | 1992 | 468206559.150061 CHINA | 1998 | 267708333.028927 CHINA | 1997 | 454050991.038623 CHINA | 1996 | 465025523.246414 CHINA | 1995 | 462429700.026001 CHINA | 1994 | 454324946.505865 CHINA | 1993 | 455345859.988373 CHINA | 1992 | 461243248.344211 EGYPT | 1998 | 270061197.713109 EGYPT | 1997 | 467517627.43095 EGYPT | 1996 | 472453705.253041 EGYPT | 1995 | 459715056.484943 EGYPT | 1994 | 463156462.508038 EGYPT | 1993 | 470277159.420408 EGYPT | 1992 | 469197381.414695 ETHIOPIA | 1998 | 274703704.72689 ETHIOPIA | 1997 | 459152330.839222 ETHIOPIA | 1996 | 461861584.869136 ETHIOPIA | 1995 | 460676907.752373 ETHIOPIA | 1994 | 460127627.088908 ETHIOPIA | 1993 | 467170823.897596 ETHIOPIA | 1992 | 462081317.370459 FRANCE | 1998 | 270376854.57532 FRANCE | 1997 | 462265378.809567 FRANCE | 1996 | 460984650.912647 FRANCE | 1995 | 465429713.064489 FRANCE | 1994 | 464098312.285399 FRANCE | 1993 | 458636615.757006 FRANCE | 1992 | 461444275.173785 GERMANY | 1998 | 277724455.675136 GERMANY | 1997 | 476364403.946548 GERMANY | 1996 | 478847075.871081 GERMANY | 1995 | 484206677.391298 GERMANY | 1994 | 481068735.7771 GERMANY | 1993 | 489344307.584832 GERMANY | 1992 | 474492174.121328 INDIA | 1998 | 279885574.822544 INDIA | 1997 | 466874687.352752 INDIA | 1996 | 468845273.610277 INDIA | 1995 | 469133199.321698 INDIA | 1994 | 472608136.956787 INDIA | 1993 | 470373800.958436 INDIA | 1992 | 460889096.181702 INDONESIA | 1998 | 272453651.983479 INDONESIA | 1997 | 460413177.60086 INDONESIA | 1996 | 453953784.390967 INDONESIA | 1995 | 458494971.47134 INDONESIA | 1994 | 468692648.517972 INDONESIA | 1993 | 464301804.596854 INDONESIA | 1992 | 458965507.110233 IRAN | 1998 | 271079961.668913 IRAN | 1997 | 459932921.413825 IRAN | 1996 | 461323896.007658 IRAN | 1995 | 468784161.14228 IRAN | 1994 | 463879315.389968 IRAN | 1993 | 460566442.464884 IRAN | 1992 | 457129165.692509 IRAQ | 1998 | 278205513.20155 IRAQ | 1997 | 476950960.382447 IRAQ | 1996 | 480627739.507285 IRAQ | 1995 | 482755065.890299 IRAQ | 1994 | 484337107.967718 IRAQ | 1993 | 471913107.424657 IRAQ | 1992 | 475742861.332921 JAPAN | 1998 | 276376781.99338 JAPAN | 1997 | 463278019.068946 JAPAN | 1996 | 472410756.237403 JAPAN | 1995 | 467498027.560966 JAPAN | 1994 | 468023998.860683 JAPAN | 1993 | 472714777.068205 JAPAN | 1992 | 473962798.766835 JORDAN | 1998 | 267001101.81484 JORDAN | 1997 | 453828590.738075 JORDAN | 1996 | 451938325.15442 JORDAN | 1995 | 455419658.116346 JORDAN | 1994 | 463906396.571202 JORDAN | 1993 | 458394966.725595 JORDAN | 1992 | 462670226.361559 KENYA | 1998 | 280802497.956461 KENYA | 1997 | 470446152.777997 KENYA | 1996 | 472563056.365358 KENYA | 1995 | 470384178.204883 KENYA | 1994 | 476401092.544262 KENYA | 1993 | 479483457.925838 KENYA | 1992 | 477534953.504668 MOROCCO | 1998 | 271040758.472028 MOROCCO | 1997 | 462202813.881262 MOROCCO | 1996 | 469439975.730884 MOROCCO | 1995 | 465414896.39798 MOROCCO | 1994 | 464932941.452395 MOROCCO | 1993 | 462960275.484778 MOROCCO | 1992 | 462610428.333871 MOZAMBIQUE | 1998 | 271588136.22875 MOZAMBIQUE | 1997 | 457838076.133778 MOZAMBIQUE | 1996 | 461855246.111167 MOZAMBIQUE | 1995 | 463729950.313515 MOZAMBIQUE | 1994 | 463647324.371647 MOZAMBIQUE | 1993 | 455079133.5977 MOZAMBIQUE | 1992 | 456022415.96268 PERU | 1998 | 276298599.608276 PERU | 1997 | 467086799.662945 PERU | 1996 | 459938805.368212 PERU | 1995 | 466462069.750732 PERU | 1994 | 455192078.832751 PERU | 1993 | 458394868.714152 PERU | 1992 | 467169011.392676 ROMANIA | 1998 | 273704473.868915 ROMANIA | 1997 | 471264652.562158 ROMANIA | 1996 | 472522127.37075 ROMANIA | 1995 | 467027542.063546 ROMANIA | 1994 | 461488055.09377 ROMANIA | 1993 | 473574397.566652 ROMANIA | 1992 | 477324311.237001 RUSSIA | 1998 | 276422717.267741 RUSSIA | 1997 | 470315514.038644 RUSSIA | 1996 | 484601507.250186 RUSSIA | 1995 | 476257429.097471 RUSSIA | 1994 | 474920993.075423 RUSSIA | 1993 | 475407097.664248 RUSSIA | 1992 | 479939865.969867 SAUDI ARABIA | 1998 | 269384518.319426 SAUDI ARABIA | 1997 | 460268490.668064 SAUDI ARABIA | 1996 | 465700100.074905 SAUDI ARABIA | 1995 | 461010631.468067 SAUDI ARABIA | 1994 | 459626037.056044 SAUDI ARABIA | 1993 | 462986101.538819 SAUDI ARABIA | 1992 | 467989255.559987 UNITED KINGDOM | 1998 | 270384740.30021 UNITED KINGDOM | 1997 | 463227598.340577 UNITED KINGDOM | 1996 | 456175520.233016 UNITED KINGDOM | 1995 | 460914082.804156 UNITED KINGDOM | 1994 | 458490656.978913 UNITED KINGDOM | 1993 | 461244304.026183 UNITED KINGDOM | 1992 | 464589288.157284 UNITED STATES | 1998 | 277260235.540824 UNITED STATES | 1997 | 476417783.073753 UNITED STATES | 1996 | 474344557.206012 UNITED STATES | 1995 | 476575099.533801 UNITED STATES | 1994 | 476254337.767096 UNITED STATES | 1993 | 472872269.962509 UNITED STATES | 1992 | 475442465.990276 VIETNAM | 1998 | 270670583.092535 VIETNAM | 1997 | 454783640.722097 VIETNAM | 1996 | 469077075.180136 VIETNAM | 1995 | 464923383.250739 VIETNAM | 1994 | 460339495.152134 VIETNAM | 1993 | 463736628.146285 VIETNAM | 1992 | 460572762.960592 (175 rows) COMMIT; COMMIT