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 | 265561722.856731 ALGERIA | 1997 | 442650881.64373 ALGERIA | 1996 | 452889932.525584 ALGERIA | 1995 | 446662126.560883 ALGERIA | 1994 | 452274828.405399 ALGERIA | 1993 | 447214759.716375 ALGERIA | 1992 | 452646554.882001 ARGENTINA | 1998 | 270463673.804019 ARGENTINA | 1997 | 467766317.590102 ARGENTINA | 1996 | 468371093.795394 ARGENTINA | 1995 | 461469637.199506 ARGENTINA | 1994 | 466855412.23847 ARGENTINA | 1993 | 473633216.507614 ARGENTINA | 1992 | 463018072.304325 BRAZIL | 1998 | 269107059.197729 BRAZIL | 1997 | 464497145.129745 BRAZIL | 1996 | 469810845.434615 BRAZIL | 1995 | 467806753.670153 BRAZIL | 1994 | 480071992.265352 BRAZIL | 1993 | 467349213.792363 BRAZIL | 1992 | 467199014.785104 CANADA | 1998 | 279680418.889523 CANADA | 1997 | 465193149.374456 CANADA | 1996 | 473218480.017847 CANADA | 1995 | 471561871.120739 CANADA | 1994 | 486115495.616886 CANADA | 1993 | 477599664.984933 CANADA | 1992 | 471132006.402562 CHINA | 1998 | 278291862.993079 CHINA | 1997 | 475398784.626498 CHINA | 1996 | 473201213.458623 CHINA | 1995 | 474657632.551396 CHINA | 1994 | 480408707.582268 CHINA | 1993 | 472849991.096814 CHINA | 1992 | 474488948.955818 EGYPT | 1998 | 272267027.150755 EGYPT | 1997 | 463811658.418581 EGYPT | 1996 | 469015892.754209 EGYPT | 1995 | 455760029.948111 EGYPT | 1994 | 454833534.596067 EGYPT | 1993 | 456663268.59842 EGYPT | 1992 | 461425192.496376 ETHIOPIA | 1998 | 268532555.502854 ETHIOPIA | 1997 | 462877701.081208 ETHIOPIA | 1996 | 464456873.770927 ETHIOPIA | 1995 | 461390040.693219 ETHIOPIA | 1994 | 463872793.235602 ETHIOPIA | 1993 | 459774215.821394 ETHIOPIA | 1992 | 465735506.110548 FRANCE | 1998 | 270744622.136248 FRANCE | 1997 | 455754432.424894 FRANCE | 1996 | 459127788.437259 FRANCE | 1995 | 451985723.73359 FRANCE | 1994 | 467444763.827776 FRANCE | 1993 | 454769952.453914 FRANCE | 1992 | 465521267.485409 GERMANY | 1998 | 272389057.862265 GERMANY | 1997 | 472842356.465243 GERMANY | 1996 | 476941077.376254 GERMANY | 1995 | 472254511.06532 GERMANY | 1994 | 468991344.339419 GERMANY | 1993 | 476838902.172198 GERMANY | 1992 | 475534470.321629 INDIA | 1998 | 279109906.997788 INDIA | 1997 | 470625758.57653 INDIA | 1996 | 476606477.471615 INDIA | 1995 | 472081832.142561 INDIA | 1994 | 473363074.897256 INDIA | 1993 | 476887329.040066 INDIA | 1992 | 474041058.00576 INDONESIA | 1998 | 269267606.669426 INDONESIA | 1997 | 456951997.21503 INDONESIA | 1996 | 460304318.392614 INDONESIA | 1995 | 462215700.018823 INDONESIA | 1994 | 457682837.700025 INDONESIA | 1993 | 460457090.238494 INDONESIA | 1992 | 453442568.654884 IRAN | 1998 | 270810955.180757 IRAN | 1997 | 463904919.55381 IRAN | 1996 | 460809163.78236 IRAN | 1995 | 461022158.789853 IRAN | 1994 | 451268678.583991 IRAN | 1993 | 455310886.324714 IRAN | 1992 | 457903606.820304 IRAQ | 1998 | 277705982.920223 IRAQ | 1997 | 472844382.610958 IRAQ | 1996 | 482024958.901231 IRAQ | 1995 | 478268668.915061 IRAQ | 1994 | 477671336.046741 IRAQ | 1993 | 480219678.109371 IRAQ | 1992 | 466320759.810763 JAPAN | 1998 | 273731148.544202 JAPAN | 1997 | 464926645.176101 JAPAN | 1996 | 462210650.291872 JAPAN | 1995 | 469672245.466527 JAPAN | 1994 | 467174646.911703 JAPAN | 1993 | 463905153.351715 JAPAN | 1992 | 457274051.487535 JORDAN | 1998 | 266759142.634655 JORDAN | 1997 | 453249253.953163 JORDAN | 1996 | 462069279.52902 JORDAN | 1995 | 455397700.655486 JORDAN | 1994 | 456019556.743234 JORDAN | 1993 | 462408282.689129 JORDAN | 1992 | 462762937.11087 KENYA | 1998 | 278994643.744915 KENYA | 1997 | 471094850.81478 KENYA | 1996 | 473819726.586908 KENYA | 1995 | 470870351.028789 KENYA | 1994 | 476158928.211832 KENYA | 1993 | 485822381.071689 KENYA | 1992 | 475562341.13071 MOROCCO | 1998 | 270013748.47429 MOROCCO | 1997 | 470864102.5378 MOROCCO | 1996 | 474430965.157732 MOROCCO | 1995 | 466366114.098139 MOROCCO | 1994 | 467619060.604909 MOROCCO | 1993 | 465136343.96078 MOROCCO | 1992 | 476822123.820103 MOZAMBIQUE | 1998 | 274192314.101455 MOZAMBIQUE | 1997 | 457630350.7915 MOZAMBIQUE | 1996 | 462668301.794357 MOZAMBIQUE | 1995 | 462019071.356086 MOZAMBIQUE | 1994 | 461477905.237543 MOZAMBIQUE | 1993 | 451893376.535397 MOZAMBIQUE | 1992 | 455709074.776719 PERU | 1998 | 271159032.304777 PERU | 1997 | 473844726.95429 PERU | 1996 | 462488058.95933 PERU | 1995 | 467554289.517879 PERU | 1994 | 459504728.064969 PERU | 1993 | 467458522.281748 PERU | 1992 | 469536282.811924 ROMANIA | 1998 | 271914089.380519 ROMANIA | 1997 | 466884313.07984 ROMANIA | 1996 | 464355267.608183 ROMANIA | 1995 | 461019997.756009 ROMANIA | 1994 | 468851425.215058 ROMANIA | 1993 | 460540926.97877 ROMANIA | 1992 | 465192924.571002 RUSSIA | 1998 | 274925845.256925 RUSSIA | 1997 | 457988543.577351 RUSSIA | 1996 | 467780457.683324 RUSSIA | 1995 | 459979613.308813 RUSSIA | 1994 | 460778595.85709 RUSSIA | 1993 | 459129799.748892 RUSSIA | 1992 | 469656650.333331 SAUDI ARABIA | 1998 | 281098634.831012 SAUDI ARABIA | 1997 | 470816146.570557 SAUDI ARABIA | 1996 | 477483738.707173 SAUDI ARABIA | 1995 | 482249282.468264 SAUDI ARABIA | 1994 | 471826216.913462 SAUDI ARABIA | 1993 | 479546806.601962 SAUDI ARABIA | 1992 | 482027599.095266 UNITED KINGDOM | 1998 | 266805292.036886 UNITED KINGDOM | 1997 | 462217636.202802 UNITED KINGDOM | 1996 | 461460603.79039 UNITED KINGDOM | 1995 | 450623496.183472 UNITED KINGDOM | 1994 | 453582094.630611 UNITED KINGDOM | 1993 | 458470916.711499 UNITED KINGDOM | 1992 | 462737875.829861 UNITED STATES | 1998 | 271375577.566659 UNITED STATES | 1997 | 473108951.759214 UNITED STATES | 1996 | 468874470.347784 UNITED STATES | 1995 | 467340015.578087 UNITED STATES | 1994 | 478291100.220141 UNITED STATES | 1993 | 470386221.374272 UNITED STATES | 1992 | 471353701.728054 VIETNAM | 1998 | 270641365.910781 VIETNAM | 1997 | 463665289.094696 VIETNAM | 1996 | 468432154.88852 VIETNAM | 1995 | 461947636.276169 VIETNAM | 1994 | 461205244.109241 VIETNAM | 1993 | 457905904.225189 VIETNAM | 1992 | 455640793.161347 (175 rows) COMMIT; COMMIT